Re: [SQL] bad plan

[Julien Cigar]

Hello Mario,

The query is in the pastie.org links, here is a copy:

SELECT
  t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
FROM (
  SELECT
    geo_id , count(*) AS frequence
  FROM
hit.context_to_context_links
  WHERE
    NOT geo_id IS NULL ANDtaxon_id= ANY (
      SELECT
        taxon_id
      FROM
        rab.ancestors
      WHERE
        ancestors.subphylum_id = 18830
      ) GROUP BY geo_id
) as t_geo
JOIN
  hit.geo_candidates gc ON gc.id = t_geo.geo_id;

Thank you,
Julien

On 04/05/2012 14:03, Mario Dankoor wrote:

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

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

<<attachment: jcigar.vcf>>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql