Re: bad plan

Julien,

It looks like you forgot to post the query.

Mario
On 2012-04-05 1:38 PM, Julien Cigar wrote:
> Hello,
>
> I have an extremely bad plan for one of my colleague's query.
> Basically PostgreSQL chooses to seq scan instead of index scan. This
> is on:
>
> antabif=# select version();
> version
> ----------------------------------------------------------------------------------------------------------
>
> PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc
> (GCC) 4.2.1 20070719 [FreeBSD], 64-bit
>
> The machines has 4GB of RAM with the following config:
> - shared_buffers: 512MB
> - effective_cache_size: 2GB
> - work_mem: 32MB
> - maintenance_work_mem: 128MB
> - default_statistics_target: 300
> - temp_buffers: 64MB
> - wal_buffers: 8MB
> - checkpoint_segments = 15
>
> The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:
>
> - http://www.pastie.org/3731956 : with default config
> - http://www.pastie.org/3731960 : this is with enable_seq_scan = off
> - http://www.pastie.org/3731962 : I tried to play on the various cost
> settings but it's doesn't change anything, except setting
> random_page_cost to 1 (which will lead to bad plans for other queries,
> so not a solution)
> - http://www.pastie.org/3732035 : with enable_hashagg and
> enable_hashjoin to false
>
> I'm currently out of idea why PostgreSQL still chooses a bad plan for
> this query ... any hint :) ?
>
> Thank you,
> Julien
>
>
>