pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQueue table that quickly grows causes query planner to choose poor plan
4+ messages / 3 participants
[nested] [flat]
* Queue table that quickly grows causes query planner to choose poor plan
@ 2018-06-27 03:45 David Wheeler <dwheeler@dgitsystems.com>
2018-06-27 18:05 ` Re: Queue table that quickly grows causes query planner to choose poor plan Justin Pryzby <pryzby@telsasoft.com>
2018-06-27 18:27 ` Re: Queue table that quickly grows causes query planner to choose poor plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: David Wheeler @ 2018-06-27 03:45 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I’m having performance trouble with a particular set of queries. It goes a bit like this
1) queue table is initially empty, and very narrow (1 bigint column)
2) we insert ~30 million rows into queue table
3) we do a join with queue table to delete from another table (delete from a using queue where a.id<http://a.id; = queue.id<http://queue.id;), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever
If I kill the query in 3 and let it run again after autoanalyze has done it’s thing then it is very quick
This queue table is empty 99% of the time, and the query in 3 runs immediately after step 2. Is there any likelyhood that tweaking the autoanalyze params would help in this case? I don’t want to explicitly analyze the table between steps 2 and three either as there are other patterns of use where for example 0 rows are inserted in step 2 and this is expected to run very very quickly. Do I have any other options?
Postgres 9.5 ATM, but an upgrade is in planning.
Thanks in advance
David Wheeler
Software developer
[cid:2C4D0888-9F8B-463F-BD54-2B60A322210C]
E dwheeler@dgitsystems.com<mailto:dwheeler@dgitsystems.com>
D +61 3 9663 3554 W http://dgitsystems.com
Level 8, 620 Bourke St, Melbourne VIC 3000.
Attachments:
[image/png] image001.png (5.1K, ../../64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queue table that quickly grows causes query planner to choose poor plan
2018-06-27 03:45 Queue table that quickly grows causes query planner to choose poor plan David Wheeler <dwheeler@dgitsystems.com>
@ 2018-06-27 18:05 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2018-06-27 18:05 UTC (permalink / raw)
To: David Wheeler <dwheeler@dgitsystems.com>; +Cc: pgsql-performance
On Wed, Jun 27, 2018 at 03:45:26AM +0000, David Wheeler wrote:
> Hi All,
>
> I’m having performance trouble with a particular set of queries. It goes a bit like this
>
> 1) queue table is initially empty, and very narrow (1 bigint column)
> 2) we insert ~30 million rows into queue table
> 3) we do a join with queue table to delete from another table (delete from a using queue where a.id<http://a.id; = queue.id<http://queue.id;), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever
If it's within a transaction, then autovacuum couldn't begin to help until it
commits. (And if it's not, then it'll be slow on its own).
It seems to me that you can't rely on autoanalyze to finish between commiting
step 2 and beginning step 3. So you're left with options like: SET
enable_nestloop=off; or manual ANALZYE (or I guess VACUUM would be adequate to
set reltuples). Maybe you can conditionalize that: if inserted>9: ANALYZE queue.
Justin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queue table that quickly grows causes query planner to choose poor plan
2018-06-27 03:45 Queue table that quickly grows causes query planner to choose poor plan David Wheeler <dwheeler@dgitsystems.com>
@ 2018-06-27 18:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-06-28 00:00 ` Re: Queue table that quickly grows causes query planner to choose poor plan David Wheeler <dwheeler@dgitsystems.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2018-06-27 18:27 UTC (permalink / raw)
To: David Wheeler <dwheeler@dgitsystems.com>; +Cc: pgsql-performance
David Wheeler <dwheeler@dgitsystems.com> writes:
> I'm having performance trouble with a particular set of queries. It goes a bit like this
> 1) queue table is initially empty, and very narrow (1 bigint column)
> 2) we insert ~30 million rows into queue table
> 3) we do a join with queue table to delete from another table (delete from a using queue where a.id<http://a.id; = queue.id<http://queue.id;), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever
Although there's no way to have any useful pg_statistic stats if you won't
do an ANALYZE, the planner nonetheless can see the table's current
physical size, and what it normally does is to multiply the last-reported
tuple density (reltuples/relpages) by the current size. So if you're
getting an "empty table" estimate anyway, I have to suppose that the
table's state involves reltuples = 0 and relpages > 0. That's not a
good place to be in; it constrains the planner to believe that the table
is in fact devoid of tuples, because that's what the last ANALYZE saw.
Now, the initial state for a freshly-created or freshly-truncated table
is *not* that. It is reltuples = 0 and relpages = 0, representing an
undefined tuple density. Given that, the planner will make some guess
about average tuple size --- which is likely to be a very good guess,
for a table with only fixed-width columns --- and then compute a rowcount
estimate using that plus the observed physical size.
So I think your problem comes from oscillating between really-empty
and not-at-all-empty, and not using an idiomatic way of going back
to the empty state. Have you tried using TRUNCATE instead of DELETE?
> This queue table is empty 99% of the time, and the query in 3 runs immediately after step 2. Is there any likelyhood that tweaking the autoanalyze params would help in this case? I don't want to explicitly analyze the table between steps 2 and three either as there are other patterns of use where for example 0 rows are inserted in step 2 and this is expected to run very very quickly. Do I have any other options?
I am not following your aversion to sticking an ANALYZE in there,
either. It's not like inserting 30 million rows would be free.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queue table that quickly grows causes query planner to choose poor plan
2018-06-27 03:45 Queue table that quickly grows causes query planner to choose poor plan David Wheeler <dwheeler@dgitsystems.com>
2018-06-27 18:27 ` Re: Queue table that quickly grows causes query planner to choose poor plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-06-28 00:00 ` David Wheeler <dwheeler@dgitsystems.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Wheeler @ 2018-06-28 00:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
Thanks for your reply, that’s very helpful and informative.
Although there's no way to have any useful pg_statistic stats if you won't
do an ANALYZE, the planner nonetheless can see the table's current
physical size, and what it normally does is to multiply the last-reported
tuple density (reltuples/relpages) by the current size. So if you're
getting an "empty table" estimate anyway, I have to suppose that the
table's state involves reltuples = 0 and relpages > 0. That's not a
good place to be in; it constrains the planner to believe that the table
is in fact devoid of tuples, because that's what the last ANALYZE saw.
That appears to be correct. I assumed that because the table was analyzed and found to be empty then the autovacuum would probably have cleared all the tuples too, but that’s not the case.
relpages | reltuples
----------+-------------
0 | 2.33795e+06
I am not following your aversion to sticking an ANALYZE in there,
either. It's not like inserting 30 million rows would be free.
There are many usage profiles for these tables. Sometimes there will be a single insert of 30 million rows, sometimes there will be several inserts of up to 100 million rows each in different threads, sometimes there will be many (~80 000) inserts of 0 rows (for which an ANALYSE is simply a waste) - I don’t want to cause undue performance penalty on the other usage profiles.
But as Justin rightly points out I can selectively ANALYSE only when > x rows are inserted, which I think is the best way forward.
David Wheeler
Software developer
[cid:2C4D0888-9F8B-463F-BD54-2B60A322210C]
E dwheeler@dgitsystems.com<mailto:dwheeler@dgitsystems.com>
D +61 3 9663 3554 W http://dgitsystems.com
Level 8, 620 Bourke St, Melbourne VIC 3000.
On 28 Jun 2018, at 4:27 am, Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
David Wheeler <dwheeler@dgitsystems.com<mailto:dwheeler@dgitsystems.com>> writes:
I'm having performance trouble with a particular set of queries. It goes a bit like this
1) queue table is initially empty, and very narrow (1 bigint column)
2) we insert ~30 million rows into queue table
3) we do a join with queue table to delete from another table (delete from a using queue where a.id<http://a.id><http://a.id; = queue.id<http://queue.id><http://queue.id;), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever
Although there's no way to have any useful pg_statistic stats if you won't
do an ANALYZE, the planner nonetheless can see the table's current
physical size, and what it normally does is to multiply the last-reported
tuple density (reltuples/relpages) by the current size. So if you're
getting an "empty table" estimate anyway, I have to suppose that the
table's state involves reltuples = 0 and relpages > 0. That's not a
good place to be in; it constrains the planner to believe that the table
is in fact devoid of tuples, because that's what the last ANALYZE saw.
Now, the initial state for a freshly-created or freshly-truncated table
is *not* that. It is reltuples = 0 and relpages = 0, representing an
undefined tuple density. Given that, the planner will make some guess
about average tuple size --- which is likely to be a very good guess,
for a table with only fixed-width columns --- and then compute a rowcount
estimate using that plus the observed physical size.
So I think your problem comes from oscillating between really-empty
and not-at-all-empty, and not using an idiomatic way of going back
to the empty state. Have you tried using TRUNCATE instead of DELETE?
This queue table is empty 99% of the time, and the query in 3 runs immediately after step 2. Is there any likelyhood that tweaking the autoanalyze params would help in this case? I don't want to explicitly analyze the table between steps 2 and three either as there are other patterns of use where for example 0 rows are inserted in step 2 and this is expected to run very very quickly. Do I have any other options?
I am not following your aversion to sticking an ANALYZE in there,
either. It's not like inserting 30 million rows would be free.
regards, tom lane
Attachments:
[image/png] image001.png (5.1K, ../../DEEA145E-68E0-4644-9105-66343FBA71A6@dgitsystems.com/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-06-28 00:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-27 03:45 Queue table that quickly grows causes query planner to choose poor plan David Wheeler <dwheeler@dgitsystems.com>
2018-06-27 18:05 ` Justin Pryzby <pryzby@telsasoft.com>
2018-06-27 18:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-06-28 00:00 ` David Wheeler <dwheeler@dgitsystems.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