Different plan and slower query on smaller Postgres table
0
votes
1
answer
38
views
Running the same query on two tables that only differ in row count (~7.8M vs ~1.4M) results in two different plans, which sounds reasonable. But the execution on the *smaller* table is 4 to 5 times slower and I would like to understand why.
The tables are defined as is:
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
image_id | bigint | | not null |
h3_cell | h3index | | not null |
created_at | timestamp with time zone | | not null |
location | geometry(PointZ,4326) | | not null |
Indexes:
"images_a_pkey" PRIMARY KEY, btree (image_id)
"images_a_created_at_idx" btree (created_at)
"images_a_h3_cell_idx" btree (h3_cell)
The query is the following
h3_cells AS (
SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell
The h3_linestring_to_cells()
function returns an array of h3index
whose size can be in some cases in the tens of thousands of values. In the examples below it returns about 50,000.
On the table with 7.8M rows, the plan and execution entries are as such (array values redacted for brevity):
Aggregate (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
-> Nested Loop (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
-> ProjectSet (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
-> Index Only Scan using images_a_h3_cell_idx on images_a (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
Heap Fetches: 354
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms
While on the smaller 1.4M rows table, the plan and execution are these:
Aggregate (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Merge Join (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Index Only Scan using images_b_h3_cell_idx on images_b ptilmi (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
Heap Fetches: 469832
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Sort (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
Sort Key: (unnest('{...}'::h3index[]))
Sort Method: quicksort Memory: 1537kB
-> ProjectSet (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms
In the case of a smaller source array, e.g. of size 25,000, the plan on the smaller table changes to the first one (nested loop) and its execution time becomes more in line with expectations (faster than on the bigger table).
I'm failing to understand what triggers this change in plan for a less efficient one.
Note that I'm using a CTE+JOIN instead of e.g. WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1))
as the resulting array is often quite large and I've found the former to often be more efficient in this case. Interestingly, with a 50,000 entries array the = ANY()
approach is faster on the smaller table, at 25,000 it's slower.
Asked by Jukurrpa
(195 rep)
Apr 7, 2025, 03:16 PM
Last activity: Apr 8, 2025, 06:06 AM
Last activity: Apr 8, 2025, 06:06 AM