From: Alex Ignatov <a.ignatov@postgrespro.ru>
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query planner wants to use seq scan
Date: Thu, 29 Oct 2015 15:27:40 +0300
Message-ID: <563210BC.9000500@postgrespro.ru> (raw)
In-Reply-To: <CAN1xZsfrZrHTcn9dGSNHpPHMMe81kzC+rZUhuewzrer4cdfrCw@mail.gmail.com>
References: <CAN1xZsc3LG3gV4j+x0Uhsh4e2Xjf1HEuAT7CnBNNPUCTXwYZhg@mail.gmail.com>
<21872.1445947392@sss.pgh.pa.us>
<CAN1xZsexuoOTjoNmFhGpOgD-BidFdh-DZTbmfDmXz=pg1sazhg@mail.gmail.com>
<CAN1xZsfrZrHTcn9dGSNHpPHMMe81kzC+rZUhuewzrer4cdfrCw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.paquet@doctolib.fr <mailto:bertrand.paquet@doctolib.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@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> a écrit :
>
> Bertrand Paquet <bertrand.paquet@doctolib.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
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: a.ignatov@postgrespro.ru, bertrand.paquet@doctolib.fr, tgl@sss.pgh.pa.us
Subject: Re: Query planner wants to use seq scan
In-Reply-To: <563210BC.9000500@postgrespro.ru>
* 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