1

Simplified, I have a table with this layout:

CREATE TABLE IF NOT EXISTS public.scans
(
    id bigint NOT NULL DEFAULT nextval('new_scans_id_seq'::regclass),
    geo geometry(Point) NOT NULL,
    kit_id integer NOT NULL,
    is_visible boolean NOT NULL DEFAULT true,
    scanned_at timestamp without time zone NOT NULL,
    created_at timestamp without time zone NOT NULL,
    guid uuid NOT NULL,
    CONSTRAINT new_scans_pkey PRIMARY KEY (scanned_at, id),
    CONSTRAINT new_scans_unique_uuid UNIQUE (scanned_at, guid)
) PARTITION BY RANGE (scanned_at)

It is hosted on AWS RDS with the following version output:

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

Partitions are defined as (for an example partition but all follow the same layout - by month):

CREATE TABLE public.new_scans_p20240201 PARTITION OF public.scans
    FOR VALUES FROM ('2024-02-01 00:00:00') TO ('2024-03-01 00:00:00')
TABLESPACE pg_default;

The JOIN table is defined with the following:

CREATE TABLE IF NOT EXISTS public.timezones
(
    gid integer NOT NULL DEFAULT nextval('timezones_gid_seq'::regclass),
    tzid character varying(80) COLLATE pg_catalog."default",
    geom geometry(MultiPolygon,4326),
    original_geom geometry,
    CONSTRAINT timezones_pkey PRIMARY KEY (gid)
)

Issuing a query like this:

select "scans"."id" as "id",
"scans".*
,"timezones"."tzid" as "timezone"
from "scans" 
join "timezones" on ST_Within(scans.geo, timezones.geom) = TRUE
where "kit_id" IN (11194,11195,11196)
and
"scans"."scanned_at"
between '2024-02-01 00:00:00' and '2024-05-31 23:59:59'
and "is_visible" = true
order by "scanned_at" desc
limit 101 offset 0

Has slow performance (2mins vs .4 sec). However, it's fast with pg_hint_plan forcing a particular index. The query plans are below with and without using pg_hint_plan. Using pg_hint_plan, I can get the execution to run quickly and it's using a better index (since the one I forced makes more sense given the query) than the planner is choosing natively. I am trying to avoid using hint_plan & fix my underlying setup, but I am not certain what to adjust in order to get this to perform the same without pg_hint_plan, as it seems like a kludge based on other comments on here.

I have set statistics on table.kit_id larger (3200). I have VACUUM ANALYZED the table (which I believe does all partitions). I can see these modified histograms in pg_stats.

WITH pg_hint_plan using:

/*+ IndexScan(scans kit_id_scanned_at_new_scans) IndexScan(timezones timezones_geom_idx) */

    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=224085.00..224125.28 rows=101 width=177) (actual time=59.014..63.479 rows=101 loops=1)
   Output: scans.id, scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta, timezones.tzid
   ->  Nested Loop  (cost=224085.00..1091137.32 rows=2174067 width=177) (actual time=59.013..63.467 rows=101 loops=1)
         Output: scans.id, scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta, timezones.tzid
         ->  Gather Merge  (cost=224084.86..230221.77 rows=51873 width=152) (actual time=58.951..62.813 rows=101 loops=1)
               Output: scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta
               Workers Planned: 3
               Workers Launched: 3
               ->  Sort  (cost=223084.82..223126.66 rows=16734 width=152) (actual time=32.883..32.949 rows=320 loops=4)
                     Output: scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta
                     Sort Key: scans.scanned_at DESC
                     Sort Method: external merge  Disk: 2352kB
                     Worker 0:  actual time=32.765..32.859 rows=391 loops=1
                       Sort Method: external merge  Disk: 2352kB
                     Worker 1:  actual time=31.517..31.587 rows=396 loops=1
                       Sort Method: external merge  Disk: 2088kB
                     Worker 2:  actual time=12.520..12.555 rows=392 loops=1
                       Sort Method: quicksort  Memory: 871kB
                     ->  Parallel Append  (cost=0.56..221910.89 rows=16734 width=152) (actual time=0.020..20.260 rows=12409 loops=4)
                           Worker 0:  actual time=0.022..17.692 rows=16188 loops=1
                           Worker 1:  actual time=0.020..16.253 rows=14350 loops=1
                           Worker 2:  actual time=0.020..10.997 rows=2915 loops=1
                           ->  Parallel Index Scan using new_scans_p20240401_kit_id_scanned_at_idx on public.new_scans_p20240401 scans_3  (cost=0.57..213745.16 rows=29387 width=152) (actual time=0.019..18.433 rows=12058 loops=4)
                                 Output: scans_3.id, scans_3.ocr, scans_3.us_state, scans_3.geo, scans_3.kit_id, scans_3.is_visible, scans_3.scanned_at, scans_3.created_at, scans_3.camera_name, scans_3.guid, scans_3.meta
                                 Index Cond: ((scans_3.kit_id = ANY ('{11194,11195,11196}'::integer[])) AND (scans_3.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_3.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                                 Filter: scans_3.is_visible
                                 Rows Removed by Filter: 1243
                                 Worker 0:  actual time=0.021..15.812 rows=16188 loops=1
                                 Worker 1:  actual time=0.019..14.596 rows=14350 loops=1
                                 Worker 2:  actual time=0.020..10.647 rows=2915 loops=1
                           ->  Parallel Index Scan using new_scans_p20240301_kit_id_scanned_at_idx on public.new_scans_p20240301 scans_2  (cost=0.56..1265.29 rows=182 width=155) (actual time=0.014..0.015 rows=0 loops=1)
                                 Output: scans_2.id, scans_2.ocr, scans_2.us_state, scans_2.geo, scans_2.kit_id, scans_2.is_visible, scans_2.scanned_at, scans_2.created_at, scans_2.camera_name, scans_2.guid, scans_2.meta
                                 Index Cond: ((scans_2.kit_id = ANY ('{11194,11195,11196}'::integer[])) AND (scans_2.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_2.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                                 Filter: scans_2.is_visible
                           ->  Parallel Index Scan using new_scans_p20240201_kit_id_scanned_at_idx on public.new_scans_p20240201 scans_1  (cost=0.56..17.74 rows=1 width=156) (actual time=0.015..0.015 rows=0 loops=1)
                                 Output: scans_1.id, scans_1.ocr, scans_1.us_state, scans_1.geo, scans_1.kit_id, scans_1.is_visible, scans_1.scanned_at, scans_1.created_at, scans_1.camera_name, scans_1.guid, scans_1.meta
                                 Index Cond: ((scans_1.kit_id = ANY ('{11194,11195,11196}'::integer[])) AND (scans_1.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_1.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                                 Filter: scans_1.is_visible
                           ->  Parallel Index Scan using new_scans_p20240501_kit_id_scanned_at_idx on public.new_scans_p20240501 scans_4  (cost=0.43..6799.04 rows=944 width=153) (actual time=0.015..1.332 rows=1405 loops=1)
                                 Output: scans_4.id, scans_4.ocr, scans_4.us_state, scans_4.geo, scans_4.kit_id, scans_4.is_visible, scans_4.scanned_at, scans_4.created_at, scans_4.camera_name, scans_4.guid, scans_4.meta
                                 Index Cond: ((scans_4.kit_id = ANY ('{11194,11195,11196}'::integer[])) AND (scans_4.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_4.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                                 Filter: scans_4.is_visible
                                 Rows Removed by Filter: 159
         ->  Index Scan using timezones_geom_idx on public.timezones  (cost=0.14..16.59 rows=1 width=4841) (actual time=0.006..0.006 rows=1 loops=101)
               Output: timezones.gid, timezones.tzid, timezones.geom, timezones.original_geom
               Index Cond: (timezones.geom ~ scans.geo)
               Filter: st_within(scans.geo, timezones.geom)
 Query Identifier: 1012795738947547320
 Planning Time: 1.258 ms
 Execution Time: 64.089 ms
(50 rows)

AND without. Notice the number of removed by filter but it's basically doing full IndexScan (across all partitions which is annoying but understandable) without considering the kit_id even though there are indices for (kit_id) and (kit_id, scanned_at) on the table.

    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.31..11948.15 rows=101 width=177) (actual time=455974.013..457742.175 rows=101 loops=1)
   Output: scans.id, scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta, timezones.tzid
   ->  Nested Loop  (cost=2.31..257139200.31 rows=2174067 width=177) (actual time=455974.011..457742.122 rows=101 loops=1)
         Output: scans.id, scans.id, scans.ocr, scans.us_state, scans.geo, scans.kit_id, scans.is_visible, scans.scanned_at, scans.created_at, scans.camera_name, scans.guid, scans.meta, timezones.tzid
         ->  Merge Append  (cost=2.17..256278284.77 rows=51873 width=152) (actual time=455973.792..457739.032 rows=101 loops=1)
               Sort Key: scans.scanned_at DESC
               ->  Index Scan Backward using new_scans_p20240201_pkey on public.new_scans_p20240201 scans_1  (cost=0.56..47637124.55 rows=1 width=156) (actual time=275208.848..275208.849 rows=0 loops=1)
                     Output: scans_1.id, scans_1.ocr, scans_1.us_state, scans_1.geo, scans_1.kit_id, scans_1.is_visible, scans_1.scanned_at, scans_1.created_at, scans_1.camera_name, scans_1.guid, scans_1.meta
                     Index Cond: ((scans_1.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_1.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                     Filter: (scans_1.is_visible AND (scans_1.kit_id = ANY ('{11194,11195,11196}'::integer[])))
                     Rows Removed by Filter: 34125583
               ->  Index Scan Backward using new_scans_p20240301_pkey on public.new_scans_p20240301 scans_2  (cost=0.56..43525294.79 rows=309 width=155) (actual time=66269.705..66269.705 rows=0 loops=1)
                     Output: scans_2.id, scans_2.ocr, scans_2.us_state, scans_2.geo, scans_2.kit_id, scans_2.is_visible, scans_2.scanned_at, scans_2.created_at, scans_2.camera_name, scans_2.guid, scans_2.meta
                     Index Cond: ((scans_2.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_2.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                     Filter: (scans_2.is_visible AND (scans_2.kit_id = ANY ('{11194,11195,11196}'::integer[])))
                     Rows Removed by Filter: 44382572
               ->  Index Scan Backward using new_scans_p20240401_pkey on public.new_scans_p20240401 scans_3  (cost=0.57..156475520.10 rows=49958 width=152) (actual time=116.262..116.262 rows=1 loops=1)
                     Output: scans_3.id, scans_3.ocr, scans_3.us_state, scans_3.geo, scans_3.kit_id, scans_3.is_visible, scans_3.scanned_at, scans_3.created_at, scans_3.camera_name, scans_3.guid, scans_3.meta
                     Index Cond: ((scans_3.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_3.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                     Filter: (scans_3.is_visible AND (scans_3.kit_id = ANY ('{11194,11195,11196}'::integer[])))
                     Rows Removed by Filter: 1279
               ->  Index Scan Backward using new_scans_p20240501_pkey on public.new_scans_p20240501 scans_4  (cost=0.43..8639567.19 rows=1605 width=153) (actual time=114378.969..116144.039 rows=101 loops=1)
                     Output: scans_4.id, scans_4.ocr, scans_4.us_state, scans_4.geo, scans_4.kit_id, scans_4.is_visible, scans_4.scanned_at, scans_4.created_at, scans_4.camera_name, scans_4.guid, scans_4.meta
                     Index Cond: ((scans_4.scanned_at >= '2024-02-01 00:00:00'::timestamp without time zone) AND (scans_4.scanned_at <= '2024-05-31 23:59:59'::timestamp without time zone))
                     Filter: (scans_4.is_visible AND (scans_4.kit_id = ANY ('{11194,11195,11196}'::integer[])))
                     Rows Removed by Filter: 5266156
         ->  Index Scan using timezones_geom_idx on public.timezones  (cost=0.14..16.59 rows=1 width=4841) (actual time=0.025..0.025 rows=1 loops=101)
               Output: timezones.gid, timezones.tzid, timezones.geom, timezones.original_geom
               Index Cond: (timezones.geom ~ scans.geo)
               Filter: st_within(scans.geo, timezones.geom)
 Query Identifier: 1012795738947547320
 Planning Time: 0.921 ms
 Execution Time: 457742.269 ms
(33 rows)
7
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented May 2 at 8:35
  • 1
    I am trying to avoid using hint_plan & fix my underlying setup, but I am not certain what to adjust in order to get this to perform the same without pg_hint_plan
    – Eric B
    Commented May 2 at 13:34
  • provide the plans as text (formatted so as to preserver indentation and line breaks), not as images.
    – jjanes
    Commented May 2 at 20:00
  • i have changed the images to text
    – Eric B
    Commented May 3 at 1:07
  • What version of postgreSQL are you using? I can't get it to reproduce this precise behavior, in that it scans the partitions in the opposite order in the absence of pg_hint_plan (which is probably a significant difference to the performance).
    – jjanes
    Commented May 3 at 16:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.