Re: Slow query with planner row strange estimation

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