Replying to the list this time (oops)...
On 04/29/11 12:33, Kevin Grittner wrote:
Also, make sure that you run ANALYZE against your temp table right
before running your query.
I did that, and also added an index to it. That had no effect on the
run time, but did fix the estimate for the temporary table.
On 04/29/11 12:12, Kevin Grittner wrote:
Out of curiosity, what do you get with?:
explain analyze
select
page_number,
ps_id,
ps_page_id
from ps_page p
where exists
(
select * from documents_ps_page d
where d.ps_page_id = p.ps_page_id
and exists
(select * from temp_document_ids t
where t.document_id = d.document_id)
)
order by ps_page_id
Merge Semi Join (cost=186501.69..107938082.91 rows=29952777 width=12)
(actual time=242801.828..244572.318 rows=5 loops=1)
Merge Cond: (p.ps_page_id = d.ps_page_id)
-> Index Scan using ps_page_pkey on ps_page p
(cost=0.00..2995637.47 rows=86141904 width=12) (actual
time=0.052..64140.510 rows=85401688 loops=1)
-> Index Scan using documents_ps_page_ps_page_id_idx on
documents_ps_page d (cost=0.00..104384546.06 rows=37358320 width=4)
(actual time=161483.657..163254.131 rows=5 loops=1)
Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
SubPlan 1
-> Seq Scan on temp_doc_ids t (cost=0.00..1.35 rows=1
width=0) (never executed)
Filter: (document_id = $0)
SubPlan 2
-> Seq Scan on temp_doc_ids t (cost=0.00..1.34 rows=5
width=35) (actual time=0.005..0.007 rows=5 loops=1)
Total runtime: 244572.432 ms
(11 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
