From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bendik Rognlien Johansen <bendik.johansen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with joins
Date: Wed, 11 Jan 2006 10:45:47 -0500
Message-ID: <13028.1136994347@sss.pgh.pa.us> (raw)
In-Reply-To: <C24D65E0-2D32-43DF-ABCD-79A47C5243AF@gmail.com>
References: <C24D65E0-2D32-43DF-ABCD-79A47C5243AF@gmail.com>
Bendik Rognlien Johansen <bendik.johansen@gmail.com> writes:
> Has anyone got any tips for speeding up this query? It currently
> takes hours to start.
Are the rowcount estimates close to reality? The plan doesn't look
unreasonable to me if they are. It might help to increase work_mem
to ensure that the hash tables don't spill to disk.
Indexes:
"people_original_is_null" btree (original) WHERE original IS NULL
This index seems poorly designed: the actual index entries are dead
weight since all of them are necessarily NULL. You might as well make
the index carry something that you frequently test in conjunction with
"original IS NULL". For instance, if this particular query is a common
case, you could replace this index with
CREATE INDEX people_deleted_original_is_null ON people(deleted)
WHERE original IS NULL;
This index is still perfectly usable for queries that only say "original
IS NULL", but it can also filter out rows with the wrong value of
deleted. Now, if there are hardly any rows with deleted = true, maybe
this won't help much for your problem. But in any case you ought to
consider whether you can make the index entries do something useful.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bendik.johansen@gmail.com
Subject: Re: Slow query with joins
In-Reply-To: <13028.1136994347@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox