From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrey Povazhnyi <w0rse.t@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow query question
In-reply-to: <48E80576-4047-436C-97DD-59EA7BE4DE34@gmail.com>
References: <48E80576-4047-436C-97DD-59EA7BE4DE34@gmail.com>
Comments: In-reply-to Andrey Povazhnyi <w0rse.t@gmail.com>
	message dated "Tue, 06 Dec 2016 17:35:23 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <26350.1481038435.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 06 Dec 2016 10:33:55 -0500
Message-ID: <26351.1481038435@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Andrey Povazhnyi <w0rse.t@gmail.com> writes:
> We=E2=80=99ve got a strange planner behavior on a query to one of our big=
ger tables after we upgraded to postgres 9.6.1 recently.

The basic problem with this query is that there are no good alternatives.
The planner believes there are about 53K rows matching the WHERE
condition.  (I assume this estimate is roughly in line with reality,
else we have different problems to talk about.)  It can either scan down
the "id" index and stop when it finds the 30th row matching WHERE, or
it can use the "symbol" index to read all 53K rows matching WHERE and
then sort them by "id".  Neither one of those is going to be speedy;
but the more rows there are matching WHERE, the better the first way
is going to look.

If you're worried about doing this a lot, it might be worth your while
to provide a 2-column index on (source, id) --- in that order --- which
would allow a query plan that directly finds the required 30 rows as
consecutive index entries.  Possibly this could replace your index on
"source" alone, depending on how much bigger the 2-col index is and
how many queries have no use for the second column.  See
https://www.postgresql.org/docs/current/static/indexes.html
particularly 11.3 - 11.5.

			regards, tom lane


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance