Re: Slow query with joins

Yes, the rowcount estimates are real, however, it has been a long
time since the last VACUUM FULL (there is never a good time).

I have clustered the tables, reindexed, analyzed, vacuumed and the
plan now looks like this:

no_people=# explain SELECT r.id AS r_id, r.firstname || ' ' ||
r.lastname AS r_name, ad.id AS ad_id, ad.type AS ad_type, ad.address
AS ad_address, ad.postalcode AS ad_postalcode, ad.postalsite AS
ad_postalsite, ad.priority AS ad_priority, ad.position[0] AS ad_lat,
ad.position[1] AS ad_lon, ad.uncertainty AS ad_uncertainty, ad.extra
AS ad_extra, ad.deleted AS ad_deleted, co.id AS co_id, co.type AS
co_type, co.value AS co_value, co.description AS co_description,
co.priority AS co_priority, co.visible AS co_visible, co.searchable
AS co_searchable, co.deleted AS co_deleted FROM people r LEFT OUTER
JOIN addresses ad ON(r.id = ad.record) LEFT OUTER JOIN contacts co ON
(r.id = co.record) WHERE NOT r.deleted AND r.original IS NULL ORDER
BY r.id;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------
Sort (cost=182866.49..182943.12 rows=30655 width=587)
Sort Key: r.id
-> Nested Loop Left Join (cost=0.00..170552.10 rows=30655
width=587)
-> Nested Loop Left Join (cost=0.00..75054.96 rows=26325
width=160)
-> Index Scan using people_deleted_original_is_null
on people r (cost=0.00..1045.47 rows=23861 width=27)
Filter: ((NOT deleted) AND (original IS NULL))
-> Index Scan using addresses_record_idx on
addresses ad (cost=0.00..3.05 rows=4 width=137)
Index Cond: ("outer".id = ad.record)
-> Index Scan using contacts_record_idx on contacts co
(cost=0.00..3.32 rows=24 width=431)
Index Cond: ("outer".id = co.record)
(10 rows)

Looks faster, but still very slow. I added limit 1000 and it has been
running for about 25 minutes now with no output. top shows:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29994 postgres 18 0 95768 78m 68m R 17.0 7.7 0:53.27 postmaster

which is unusual, I usually get 99.9 %cpu for just about any query,
which leads me to believe this is disk related.

postgresql.conf:
shared_buffers = 8192
work_mem = 8192
maintenance_work_mem = 524288

Hardware 2x2.8GHz cpu
1GB ram

Could this be an issue related to lack of VACUUM FULL? The tables get
a lot of updates.

Thank you very much so far!

On Jan 11, 2006, at 4:45 PM, Tom Lane wrote:

> Bendik Rognlien Johansen <bendik(dot)johansen(at)gmail(dot)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