Re: Query planner wants to use seq scan

Yes, the three fields index AND vacuum solve the issue.

Regards,

Bertrand

2015-10-29 13:27 GMT+01:00 Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>:

>
>
> On 27.10.2015 23:56, Bertrand Paquet wrote:
>
> So,
>
> Tonight, the index on the three field is used, may be my yesterday vacuum
> updated stats.
>
> Thx you for your help.
>
> Regards,
>
> Bertrand
>
>
>
>
> 2015-10-27 18:33 GMT+01:00 Bertrand Paquet <bertrand(dot)paquet(at)doctolib(dot)fr>:
>
>> Hi tom,
>>
>> I did the test yesterday with an index on the three fields, and with a
>> partial index on organization and status and where is null condition on
>> handled. I saw no modification on query plan.
>> May be I forgot to analyze vacuum after. I will retry tonight.
>>
>> I use a btree index. Is it the good solution, even with the In clause ?
>>
>> Regards,
>>
>> Bertrand
>>
>> Le mardi 27 octobre 2015, Tom Lane < <tgl(at)sss(dot)pgh(dot)pa(dot)us>tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> a écrit :
>>
>>> Bertrand Paquet <bertrand(dot)paquet(at)doctolib(dot)fr> writes:
>>> > We have a slow query. After analyzing, the planner decision seems to be
>>> > discutable : the query is faster when disabling seqscan. See below the
>>> two
>>> > query plan, and an extract from pg_stats.
>>>
>>> > Any idea about what to change to help the planner ?
>>>
>>> Neither one of those plans is very good: you're just hoping that the
>>> Filter condition will let a tuple through sooner rather than later.
>>>
>>> If you care about the performance of this type of query, I'd consider
>>> creating an index on (organization_id, status, handled_by) so that all
>>> the conditions can be checked in the index.
>>>
>>> regards, tom lane
>>>
>>
> Hello Bertrand once again!
> What's your status? Does the plan changed after deploying three field
> index ?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>