pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL performance issue (postgresql chooses a bad plan when a better one is available)
6+ messages / 3 participants
[nested] [flat]
* SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-22 13:10 Chris Stephens <cstephens16@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Stephens @ 2021-03-22 13:10 UTC (permalink / raw)
To: pgsql-performance
AWS RDS v12
The following SQL takes ~25 seconds to run. I'm relatively new to postgres
but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's
materializing the entire EXISTS subquery for each row returned by the rest
of the query before probing for plate_384_id existence. postgres is
choosing sequential scans on sample_plate_384 and test_result when
suitable, efficient indexes exist. a re-written query produces a much
better plan (https://explain.depesz.com/s/zXJ6). Executing the EXISTS
portion of the query with an explicit PLATE_384_ID yields the execution
plan we want as well (https://explain.depesz.com/s/3QAK). unnesting the
EXISTS and adding a DISTINCT on the result also yields a better plan.
I've tried tried the following:
disable parallel
set join_collapse_limit=1 and played with order of EXISTS/NOT EXISTS
changed work_mem and enable_material to see if that had any effect
VACUUM FULL'd TEST_RESULT and SAMPLE_PLATE_384
created a stats object on (sample_id, sample_plate_384_id) for both
TEST_RESULT and SAMPLE_PLATE_384 to see if that would help (they increment
fairly consistently with each other)
I'm out of ideas on how to convince postgres to choose a better plan. any
and all help/suggestions/explanations would be greatly appreciated. the
rewritten SQL performs sufficiently well but i'd like to understand why
postgres is doing this and what to do about it so i can't tackle the next
SQL performance issue with a little more knowledge.
SELECT count(*) AS "count" FROM "plate_384_scan"
WHERE NOT EXISTS (SELECT 1 FROM "plate_384_scan" AS "plate_384_scan_0"
WHERE "plate_384_scan_0"."ts" > "plate_384_scan"."ts" AND
"plate_384_scan_0"."plate_384_id" = "plate_384_scan"."plate_384_id")
AND EXISTS (SELECT 1 FROM "sample_plate_384" INNER JOIN "test_result"
USING ("sample_plate_384_id", "sample_id") WHERE "test_result" IS NULL AND
"plate_384_scan_id" = "plate_384_scan"."plate_384_scan_id")
AND NOT EXISTS (SELECT 1 FROM "plate_384_abandoned" WHERE "plate_384_id"
= "plate_384_scan"."plate_384_id");
[limsdb_dev] # SELECT relname, relpages, reltuples, relallvisible, relkind,
relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class
WHERE relname in ('sample_plate_384','test_result',
'plate_384_scan','plate_384_abandoned') order by 1;
relname | relpages | reltuples | relallvisible | relkind |
relnatts | relhassubclass | reloptions | pg_table_size
---------------------+----------+-----------+---------------+---------+----------+----------------+------------+---------------
plate_384_abandoned | 1 | 16 | 0 | r |
4 | f | (null) | 16384
plate_384_scan | 13 | 1875 | 0 | r |
5 | f | (null) | 131072
sample_plate_384 | 3827 | 600701 | 0 | r |
9 | f | (null) | 31350784
test_result | 4900 | 599388 | 0 | r |
8 | f | (null) | 40140800
(4 rows)
Time: 44.405 ms
[limsdb_dev] # \d plate_384_abandoned
Table "lab_data.plate_384_abandoned"
Column | Type | Collation | Nullable |
Default
--------------+--------------------------+-----------+----------+-------------------
plate_384_id | integer | | not null |
reason | text | | not null |
tech_id | integer | | |
ts | timestamp with time zone | | not null |
CURRENT_TIMESTAMP
Indexes:
"plate_384_abandoned_pkey" PRIMARY KEY, btree (plate_384_id)
Foreign-key constraints:
"plate_384_abandoned_plate_384_id_fkey" FOREIGN KEY (plate_384_id)
REFERENCES plate_384(plate_384_id)
"plate_384_abandoned_tech_id_fkey" FOREIGN KEY (tech_id) REFERENCES
tech(tech_id)
[limsdb_dev] # \d plate_384_scan
Table
"lab_data.plate_384_scan"
Column | Type | Collation | Nullable |
Default
-------------------+--------------------------+-----------+----------+-----------------------------------------------------------
plate_384_scan_id | integer | | not null |
nextval('plate_384_scan_plate_384_scan_id_seq'::regclass)
plate_384_id | integer | | not null |
equipment_id | integer | | not null |
tech_id | integer | | not null |
ts | timestamp with time zone | | not null |
CURRENT_TIMESTAMP
Indexes:
"pk_plate_384_scan" PRIMARY KEY, btree (plate_384_scan_id)
"plate_384_scan_idx001" btree (ts, plate_384_scan_id)
"plate_384_scan_idx002" btree (plate_384_id, ts)
Foreign-key constraints:
"fk_plate_384_scan_equipment_id" FOREIGN KEY (equipment_id) REFERENCES
equipment(equipment_id)
"fk_plate_384_scan_plate_384_id" FOREIGN KEY (plate_384_id) REFERENCES
plate_384(plate_384_id)
"fk_plate_384_scan_tech_id" FOREIGN KEY (tech_id) REFERENCES
tech(tech_id)
Referenced by:
TABLE "sample_plate_384" CONSTRAINT
"fk_sample_plate_384_plate_384_scan_id" FOREIGN KEY (plate_384_scan_id)
REFERENCES plate_384_scan(plate_384_scan_id)
TABLE "sample_plate_384_removed" CONSTRAINT
"sample_plate_384_removed_plate_384_scan_id_fkey" FOREIGN KEY
(plate_384_scan_id) REFERENCES plate_384_scan(plate_384_scan_id)
TABLE "test_result_file" CONSTRAINT
"test_result_file_plate_384_scan_id_fkey" FOREIGN KEY (plate_384_scan_id)
REFERENCES plate_384_scan(plate_384_scan_id)
[limsdb_dev] # \d sample_plate_384
Table "lab_data.sample_plate_384"
Column | Type | Collation | Nullable |
Default
---------------------+---------+-----------+----------+---------------------------------------------------------------
sample_plate_384_id | integer | | not null |
nextval('sample_plate_384_sample_plate_384_id_seq'::regclass)
sample_id | integer | | not null |
plate_384_scan_id | integer | | not null |
plate_384_well | integer | | not null |
Indexes:
"pk_sample_plate_384" PRIMARY KEY, btree (sample_plate_384_id)
"sample_plate_384_idx001" btree (sample_id, sample_plate_384_id)
"sample_plate_384_idx002" btree (sample_id, sample_plate_384_id,
plate_384_scan_id)
"sample_plate_384_idx003" btree (plate_384_scan_id, sample_plate_384_id)
"sample_plate_384_idx004" btree (plate_384_scan_id,
sample_plate_384_id, sample_id)
"sample_plate_384_plate_384_scan_id_plate_384_well_idx" UNIQUE, btree
(plate_384_scan_id, plate_384_well)
Foreign-key constraints:
"fk_sample_plate_384_plate_384_scan_id" FOREIGN KEY (plate_384_scan_id)
REFERENCES plate_384_scan(plate_384_scan_id)
"fk_sample_plate_384_sample" FOREIGN KEY (sample_id) REFERENCES
sample(sample_id)
Referenced by:
TABLE "sample_plate_96_plate_384" CONSTRAINT
"fk_sample_plate_96_plate_384_sample_plate_384_id" FOREIGN KEY
(sample_plate_384_id) REFERENCES sample_plate_384(sample_plate_384_id)
TABLE "test_result" CONSTRAINT "fk_test_result_sample_plate_384"
FOREIGN KEY (sample_plate_384_id) REFERENCES
sample_plate_384(sample_plate_384_id)
Statistics objects:
"lab_data"."sp384_stats" (ndistinct, dependencies, mcv) ON
sample_plate_384_id, sample_id FROM sample_plate_384
[limsdb_dev] # \d test_result
Table "lab_data.test_result"
Column | Type | Collation | Nullable |
Default
---------------------+--------------------------+-----------+----------+-----------------------------------------------------
test_result_id | integer | | not null |
nextval('test_result_test_result_id_seq'::regclass)
sample_plate_384_id | integer | | not null |
sample_id | integer | | not null |
equipment_id | integer | | |
test_result | character varying(100) | | |
final_result_flag | boolean | | |
tech_id | integer | | |
ts | timestamp with time zone | | |
CURRENT_TIMESTAMP
Indexes:
"pk_test_result" PRIMARY KEY, btree (test_result_id)
"test_result_idx001" btree (sample_id, ts, final_result_flag,
test_result)
"test_result_idx002" btree (ts, final_result_flag, test_result,
sample_id)
"test_result_idx003" btree (ts, sample_id)
"test_result_idx004" btree (sample_id, sample_plate_384_id)
"test_result_idx005" btree (sample_plate_384_id)
"test_result_idx006" btree (sample_plate_384_id, sample_id, test_result)
"test_result_sample_plate_384_id_idx" UNIQUE, btree
(sample_plate_384_id)
Foreign-key constraints:
"fk_test_result_equipment" FOREIGN KEY (equipment_id) REFERENCES
equipment(equipment_id)
"fk_test_result_sample" FOREIGN KEY (sample_id) REFERENCES
sample(sample_id)
"fk_test_result_sample_plate_384" FOREIGN KEY (sample_plate_384_id)
REFERENCES sample_plate_384(sample_plate_384_id)
"fk_test_result_tech" FOREIGN KEY (tech_id) REFERENCES tech(tech_id)
Referenced by:
TABLE "test_result_detail" CONSTRAINT "fk_test_result_detail" FOREIGN
KEY (test_result_id) REFERENCES test_result(test_result_id)
TABLE "reported_test_result" CONSTRAINT
"reported_test_result_test_result_id_fkey" FOREIGN KEY (test_result_id)
REFERENCES test_result(test_result_id)
Statistics objects:
"lab_data"."test_result_stats" (ndistinct, dependencies, mcv) ON
sample_plate_384_id, sample_id FROM test_result
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-22 14:54 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Chris Stephens <cstephens16@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2021-03-22 14:54 UTC (permalink / raw)
To: Chris Stephens <cstephens16@gmail.com>; pgsql-performance
On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote:
> The following SQL takes ~25 seconds to run. I'm relatively new to postgres
> but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's
> materializing the entire EXISTS subquery for each row returned by the rest
> of the query before probing for plate_384_id existence. postgres is
> choosing sequential scans on sample_plate_384 and test_result when suitable,
> efficient indexes exist. a re-written query produces a much better plan
> (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion of the
> query with an explicit PLATE_384_ID yields the execution plan we want as
> well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and adding
> a DISTINCT on the result also yields a better plan.
Great! Then use one of the rewritten queries.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-22 17:28 Chris Stephens <cstephens16@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Stephens @ 2021-03-22 17:28 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance
we are but i was hoping to get a better understanding of where the
optimizer is going wrong and what i can do about it.
chris
On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote:
> > The following SQL takes ~25 seconds to run. I'm relatively new to
> postgres
> > but the execution plan (https://explain.depesz.com/s/N4oR) looks like
> it's
> > materializing the entire EXISTS subquery for each row returned by the
> rest
> > of the query before probing for plate_384_id existence. postgres is
> > choosing sequential scans on sample_plate_384 and test_result when
> suitable,
> > efficient indexes exist. a re-written query produces a much better plan
> > (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion of
> the
> > query with an explicit PLATE_384_ID yields the execution plan we want as
> > well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and
> adding
> > a DISTINCT on the result also yields a better plan.
>
> Great! Then use one of the rewritten queries.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-22 21:39 Hannu Krosing <hannuk@google.com>
parent: Chris Stephens <cstephens16@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Hannu Krosing @ 2021-03-22 21:39 UTC (permalink / raw)
To: Chris Stephens <cstephens16@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
you can play around various `enable_*` flags to see if disabling any
of these will *maybe* yield the plan you were expecting, and then
check the costs in EXPLAIN to see if the optimiser also thinks this
plan is cheaper.
On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens <cstephens16@gmail.com> wrote:
>
> we are but i was hoping to get a better understanding of where the optimizer is going wrong and what i can do about it.
>
> chris
>
>
> On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>>
>> On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote:
>> > The following SQL takes ~25 seconds to run. I'm relatively new to postgres
>> > but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's
>> > materializing the entire EXISTS subquery for each row returned by the rest
>> > of the query before probing for plate_384_id existence. postgres is
>> > choosing sequential scans on sample_plate_384 and test_result when suitable,
>> > efficient indexes exist. a re-written query produces a much better plan
>> > (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion of the
>> > query with an explicit PLATE_384_ID yields the execution plan we want as
>> > well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and adding
>> > a DISTINCT on the result also yields a better plan.
>>
>> Great! Then use one of the rewritten queries.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-23 15:21 Chris Stephens <cstephens16@gmail.com>
parent: Hannu Krosing <hannuk@google.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Stephens @ 2021-03-23 15:21 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
"set enable_material=false;" produces an efficient plan. good to know there
are *some* knobs to turn when the optimizer comes up with a bad plan. would
be awesome if you could lock that plan into place w/out altering the
variable.
thanks for the help Hannu!
On Mon, Mar 22, 2021 at 4:39 PM Hannu Krosing <hannuk@google.com> wrote:
> you can play around various `enable_*` flags to see if disabling any
> of these will *maybe* yield the plan you were expecting, and then
> check the costs in EXPLAIN to see if the optimiser also thinks this
> plan is cheaper.
>
>
> On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens <cstephens16@gmail.com>
> wrote:
> >
> > we are but i was hoping to get a better understanding of where the
> optimizer is going wrong and what i can do about it.
> >
> > chris
> >
> >
> > On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> >>
> >> On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote:
> >> > The following SQL takes ~25 seconds to run. I'm relatively new to
> postgres
> >> > but the execution plan (https://explain.depesz.com/s/N4oR) looks
> like it's
> >> > materializing the entire EXISTS subquery for each row returned by
> the rest
> >> > of the query before probing for plate_384_id existence. postgres is
> >> > choosing sequential scans on sample_plate_384 and test_result when
> suitable,
> >> > efficient indexes exist. a re-written query produces a much better
> plan
> >> > (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion
> of the
> >> > query with an explicit PLATE_384_ID yields the execution plan we
> want as
> >> > well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and
> adding
> >> > a DISTINCT on the result also yields a better plan.
> >>
> >> Great! Then use one of the rewritten queries.
> >>
> >> Yours,
> >> Laurenz Albe
> >> --
> >> Cybertec | https://www.cybertec-postgresql.com
> >>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
@ 2021-03-23 23:20 Hannu Krosing <hannuk@google.com>
parent: Chris Stephens <cstephens16@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Hannu Krosing @ 2021-03-23 23:20 UTC (permalink / raw)
To: Chris Stephens <cstephens16@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
When I do serious database development I try to use database functions
as much as possible.
You can attach any flag value to a function in which case it gets set
when the function is running,
In your case you could probably wrap your query into an set-returning
`LANGUAGE SQL` function [1] and then include
`SET enable_material=false`
as part of the `CREATE FUNCTION` [2]
------
[1] https://www.postgresql.org/docs/current/xfunc-sql.html
[2] https://www.postgresql.org/docs/13/sql-createfunction.html
On Tue, Mar 23, 2021 at 4:22 PM Chris Stephens <cstephens16@gmail.com> wrote:
>
> "set enable_material=false;" produces an efficient plan. good to know there are *some* knobs to turn when the optimizer comes up with a bad plan. would be awesome if you could lock that plan into place w/out altering the variable.
>
> thanks for the help Hannu!
>
> On Mon, Mar 22, 2021 at 4:39 PM Hannu Krosing <hannuk@google.com> wrote:
>>
>> you can play around various `enable_*` flags to see if disabling any
>> of these will *maybe* yield the plan you were expecting, and then
>> check the costs in EXPLAIN to see if the optimiser also thinks this
>> plan is cheaper.
>>
>>
>> On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens <cstephens16@gmail.com> wrote:
>> >
>> > we are but i was hoping to get a better understanding of where the optimizer is going wrong and what i can do about it.
>> >
>> > chris
>> >
>> >
>> > On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>> >>
>> >> On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote:
>> >> > The following SQL takes ~25 seconds to run. I'm relatively new to postgres
>> >> > but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's
>> >> > materializing the entire EXISTS subquery for each row returned by the rest
>> >> > of the query before probing for plate_384_id existence. postgres is
>> >> > choosing sequential scans on sample_plate_384 and test_result when suitable,
>> >> > efficient indexes exist. a re-written query produces a much better plan
>> >> > (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion of the
>> >> > query with an explicit PLATE_384_ID yields the execution plan we want as
>> >> > well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and adding
>> >> > a DISTINCT on the result also yields a better plan.
>> >>
>> >> Great! Then use one of the rewritten queries.
>> >>
>> >> Yours,
>> >> Laurenz Albe
>> >> --
>> >> Cybertec | https://www.cybertec-postgresql.com
>> >>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-03-23 23:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-22 13:10 SQL performance issue (postgresql chooses a bad plan when a better one is available) Chris Stephens <cstephens16@gmail.com>
2021-03-22 14:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-22 17:28 ` Chris Stephens <cstephens16@gmail.com>
2021-03-22 21:39 ` Hannu Krosing <hannuk@google.com>
2021-03-23 15:21 ` Chris Stephens <cstephens16@gmail.com>
2021-03-23 23:20 ` Hannu Krosing <hannuk@google.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