SQL performance issue (postgresql chooses a bad plan when a better one is available)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux