Postgres Pro
Downloads
Home   >   mailing lists

Re: bad planning with 75% effective_cache_size - Mailing list pgsql-performance

From Віталій Тимчишин
Subject Re: bad planning with 75% effective_cache_size
Date
Msg-id CABWW-d0bmpNXuztM=JT9+nGCOfYpCcwR7q20Or17VuXnvoMy=g@mail.gmail.com
Whole thread Raw
In response to bad planning with 75% effective_cache_size  (Istvan Endredy <istvan.endredy@gmail.com>)
List pgsql-performance
Tree view
How about

with par_ as (select * from product_parent where parent_name like 'aa%' )
select distinct product_code from product p_
inner join par_ on p_.parent_id=par_.id 
limit 2

?


2012/4/3 Istvan Endredy <istvan.endredy@gmail.com>
Hi,

i've ran into a planning problem.


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


If effective_cache_size is smaller (32MB), planning is ok and query is fast. (10ms)
In the worst case (effective_cache_size=6GB) the speed depends on the value of 'limit' (in select): if it is smaller, query is slower. (12ms)



--
Best regards,
 Vitalii Tymchyshyn

pgsql-performance by date:

Previous
From: Istvan Endredy
Date:
Subject: Re: bad planning with 75% effective_cache_size
Next
From: Eyal Wilde
Date:
Subject: Re: scale up (postgresql vs mssql)