From: damien hostin <damien.hostin@axege.com>
To: phb07 <phb07@apra.asso.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with planner row strange estimation
Date: Tue, 13 Jul 2010 10:51:47 +0200
Message-ID: <4C3C2923.8070409@axege.com> (raw)
In-Reply-To: <4C3B7C0D.6070404@apra.asso.fr>
References: <4C2DA7FB.1080705@axege.com>
<4C318983.6040600@axege.com>
<4C332604.1040409@axege.com>
<4C3491AA.60301@axege.com>
<AANLkTimauId9FvRbYGvnpcnwBSk7Yuyd6LREavGM72Eu@mail.gmail.com>
<4C36F647.4060902@axege.com>
<AANLkTilpsAwBy2x6Sy8EBYaprOoGp8H0REt3ZiH35cHn@mail.gmail.com>
<AANLkTinCxi1kURyjrnY3-UmZShUe8Aw-i4vTiasbdX-m@mail.gmail.com>
<4C3B7C0D.6070404@apra.asso.fr>
phb07 a écrit :
>
> Dimitri a écrit :
>> It's probably one of the cases when having HINTS in PostgreSQL may be
>> very helpful..
>>
>> SELECT /*+ enable_nestloop=off */ ... FROM ...
>>
>> will just fix this query without impacting other queries and without
>> adding any additional instructions into the application code..
>>
>> So, why there is a such resistance to implement hints withing SQL
>> queries in PG?..
>>
>> Rgds,
>> -Dimitri
>>
>>
> +1.
> Another typical case when it would be helpful is with setting the
> cursor_tuple_fraction GUC variable for a specific statement, without
> being obliged to issue 2 SET statements, one before the SELECT and the
> other after.
>
>
I remember that the "dimension" columns of the fact table have indexes
like with "WHERE IS NOT NULL" on the column indexed. Example:
CREATE INDEX dwhinv_pd2_idx
ON dwhinv
USING btree
(dwhinv_p2rfodstide)
TABLESPACE tb_index
WHERE dwhinv_p2rfodstide IS NOT NULL;
Is the where clause being used to select the sample rows on which the
stats will be calculated or just used to exclude values after collecting
stat ? As I am writing I realize there's must be no link between a table
column stats and an index a the same column. (By the way, If I used is
not null on each column with such an index, it changes nothing)
About the oracle-like hints, it does not really help, because the query
is generated in an external jar that I should fork to include the
modification. I would prefer forcing a plan based on the query hashcode,
but this does not fix what make the planner goes wrong.
--
HOSTIN Damien - Equipe R&D
Tel:+33(0)4 63 05 95 40
Société Axège
23 rue Saint Simon
63000 Clermont Ferrand
www.axege.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: damien.hostin@axege.com, phb07@apra.asso.fr
Subject: Re: Slow query with planner row strange estimation
In-Reply-To: <4C3C2923.8070409@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox