pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query question
3+ messages / 2 participants
[nested] [flat]
* Slow query question
@ 2016-12-06 14:35 Andrey Povazhnyi <w0rse.t@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrey Povazhnyi @ 2016-12-06 14:35 UTC (permalink / raw)
To: pgsql-performance
Hello, List.
We’ve got a strange planner behavior on a query to one of our bigger tables after we upgraded to postgres 9.6.1 recently.
The table (schema here http://pastebin.com/nRAny4bw <http://pastebin.com/nRAny4bw;) has 28m+ rows and is used to store chat messages for different chat rooms (symbol is the room id).
The query is as follows:
SELECT "tv_site_chathistory"."source" FROM "tv_site_chathistory" WHERE "tv_site_chathistory"."symbol" = ’pm_OmoGVzBdyPnpYkXD' ORDER BY "tv_site_chathistory"."id" DESC LIMIT 30;
(explain analyze is here https://explain.depesz.com/s/iyT <https://explain.depesz.com/s/iyT;)
For some reason planner chooses to scan using pkey index instead of index on symbol column. Most times it uses the right index, but for this particular ‘symbol’ value is resorts to pkey scan. One possible clue could be that last 30 rows with this particular symbol are spanning some relatively large time of creation.
Any advice would be greatly appreciated!=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow query question
@ 2016-12-06 15:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrey Povazhnyi <w0rse.t@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2016-12-06 15:33 UTC (permalink / raw)
To: Andrey Povazhnyi <w0rse.t@gmail.com>; +Cc: pgsql-performance
Andrey Povazhnyi <w0rse.t@gmail.com> writes:
> We’ve got a strange planner behavior on a query to one of our bigger 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow query question
@ 2016-12-07 09:51 Andrey Povazhnyi <w0rse.t@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Andrey Povazhnyi @ 2016-12-07 09:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom,
Thank you for a thorough answer. We’ll try the 2-column index.
Regards,
Andrey Povazhnyi
> On Dec 6, 2016, at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrey Povazhnyi <w0rse.t@gmail.com> writes:
>> We’ve got a strange planner behavior on a query to one of our bigger 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-12-07 09:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-12-06 14:35 Slow query question Andrey Povazhnyi <w0rse.t@gmail.com>
2016-12-06 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-12-07 09:51 ` Andrey Povazhnyi <w0rse.t@gmail.com>
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