From: | Istvan Endredy <istvan(dot)endredy(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: bad planning with 75% effective_cache_size |
Date: | 2012-04-17 09:49:57 |
Message-ID: | CAEcxehpgASdr9AAxcUNJhG+DqDx0i2VupY_Jj_MVhxDRg8VJcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
thanks for the suggestion, but it didn't help. We have tried it earlier.
7500ms
http://explain.depesz.com/s/ctn
ALTER TABLE product_parent ALTER COLUMN parent_name SET STATISTICS 1000;
ALTER TABLE product ALTER COLUMN parent_id SET STATISTICS 1000;
ANALYZE product_parent;
ANALYZE product;
query was:
select distinct product_code from product p_
inner join product_parent par_ on p_.parent_id=par_.id
where par_.parent_name like 'aa%' limit 2
i've played with the query, and found an interesting behaviour: its
speed depends on value of limit:
select ... limit 2; => 1500ms
select ... limit 20; => 14ms (http://explain.depesz.com/s/4iL)
select ... limit 50; => 17ms
These were with high effective_cache_size (6GB). Somehow it uses good
planning in these cases.
If it helps i can send the db to repro (53M).
Any tips to try?
Thanks in advance,
Istvan
From | Date | Subject | |
---|---|---|---|
Next Message | Віталій Тимчишин | 2012-04-17 17:12:26 | Re: SeqScan with full text search |
Previous Message | Merlin Moncure | 2012-04-16 19:38:10 | Re: SeqScan with full text search |