Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZrmKf-0003ku-E0 for pgsql-performance@arkaria.postgresql.org; Thu, 29 Oct 2015 12:29:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZrmKf-00085F-0Y for pgsql-performance@arkaria.postgresql.org; Thu, 29 Oct 2015 12:29:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZrmIz-0005rJ-CY for pgsql-performance@postgresql.org; Thu, 29 Oct 2015 12:27:45 +0000 Received: from newmail.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZrmIv-0006UL-TJ for pgsql-performance@postgresql.org; Thu, 29 Oct 2015 12:27:44 +0000 Received: from [192.168.27.120] (unknown [192.168.27.1]) by mail.postgrespro.ru (Postfix) with ESMTPSA id 729EC21C4164; Thu, 29 Oct 2015 15:27:40 +0300 (MSK) Subject: Re: Query planner wants to use seq scan To: Bertrand Paquet , Tom Lane References: <21872.1445947392@sss.pgh.pa.us> Cc: "pgsql-performance@postgresql.org" From: Alex Ignatov Message-ID: <563210BC.9000500@postgrespro.ru> Date: Thu, 29 Oct 2015 15:27:40 +0300 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:38.0) Gecko/20100101 Thunderbird/38.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------030100050602060303060000" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------030100050602060303060000 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > >: > > 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 > a écrit : > > Bertrand Paquet 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 --------------030100050602060303060000 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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>:
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> 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
--------------030100050602060303060000--