On 05/02/11 08:11, Tom Lane wrote:
Wayne Conrad writes:
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
Tom Lane wrote:
> Wayne Conrad writes:
>> Total runtime: 244572.432 ms
> I'll take a look at fixing that, but not sure if it'll be
> reasonable to back-patch or not. In the meantime, you need to
> look into restructuring the query to avoid nesting the EXISTS
> probes, if possible.
Wayne,
Wayne Conrad writes:
> 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
>> a
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 temp
"Kevin Grittner" writes:
> Also, make sure that you run ANALYZE against your temp table right
> before running your query.
Yeah. It's fairly hard to credit that temp_document_ids has any stats
given the way-off estimates for it. Keep in mind that autovacuum
can't help you on temp tables: since
Wayne Conrad wrote:
> -> Seq Scan on temp_doc_ids
> (cost=0.00..23.48 rows=1310 width=32)
> (actual time=0.005..0.005 rows=5 loops=1)
Also, make sure that you run ANALYZE against your temp table right
before running your query.
-Kevin
--
Sent via pgsql-performance m
Wayne Conrad wrote:
> select page_number, ps_id, ps_page_id from ps_page where
> ps_page_id in (select ps_page_id from documents_ps_page where
> document_id in (select document_id from temp_doc_ids)) order by
> ps_page_id;
> [estimated rows=34398932; actual rows=5]
> We tried increasing (fro
