Julien,<br /><br /> It looks like you forgot to post the query.<br /><br /> Mario<br /> On 2012-04-05 1:38 PM, Julien
Cigarwrote: <blockquote cite="mid:4F7D843E.4060304@ulb.ac.be" type="cite">Hello, <br /><br /> I have an extremely bad
planfor one of my colleague's query. Basically PostgreSQL chooses to seq scan instead of index scan. This is on: <br
/><br/> antabif=# select version(); <br /> version <br />
----------------------------------------------------------------------------------------------------------<br />
PostgreSQL9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit <br /><br />
Themachines has 4GB of RAM with the following config: <br /> - shared_buffers: 512MB <br /> - effective_cache_size: 2GB
<br/> - work_mem: 32MB <br /> - maintenance_work_mem: 128MB <br /> - default_statistics_target: 300 <br /> -
temp_buffers:64MB <br /> - wal_buffers: 8MB <br /> - checkpoint_segments = 15 <br /><br /> The tables have been
ANALYZE'd.I've put the EXPLAIN ANALYZE on: <br /><br /> - <a class="moz-txt-link-freetext"
href="http://www.pastie.org/3731956">http://www.pastie.org/3731956</a>: with default config <br /> - <a
class="moz-txt-link-freetext"href="http://www.pastie.org/3731960">http://www.pastie.org/3731960</a> : this is with
enable_seq_scan= off <br /> - <a class="moz-txt-link-freetext"
href="http://www.pastie.org/3731962">http://www.pastie.org/3731962</a>: I tried to play on the various cost settings
butit's doesn't change anything, except setting random_page_cost to 1 (which will lead to bad plans for other queries,
sonot a solution) <br /> - <a class="moz-txt-link-freetext"
href="http://www.pastie.org/3732035">http://www.pastie.org/3732035</a>: with enable_hashagg and enable_hashjoin to
false<br /><br /> I'm currently out of idea why PostgreSQL still chooses a bad plan for this query ... any hint :) ?
<br/><br /> Thank you, <br /> Julien <br /><br /><br /><fieldset class="mimeAttachmentHeader"></fieldset><br /><pre
wrap="">
</pre></blockquote><br />