Skip site navigation (1) Skip section navigation (2)

Re: bad planning with 75% effective_cache_size

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 (view raw or flat)
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

Responses

pgsql-performance by date

Next:From: Віталій ТимчишинDate: 2012-04-17 17:12:26
Subject: Re: SeqScan with full text search
Previous:From: Merlin MoncureDate: 2012-04-16 19:38:10
Subject: Re: SeqScan with full text search

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group