/
Query Analysis for Hub Partition

Inserting 10k rows into partition table for query analysis



1explain analyze SELECT * 2FROM partition 3WHERE ST_Contains(bounds, ST_SetSRID (ST_MakePoint ( 11.861345816761713 ,111.91293818642419), 4326))

Disabling all indexs, to have raw comparison between using bounds and polygon columns.

Using bounds:

Using polygon:

Enabling indexes:

Using polygon index

Using bounds index


Tested for 10 more samples and observed that polygon index is always faster than bounds index


First finding all possible valid partition using bounds then filtering actual valid partition using polygon column

1 WITH possible_partitions AS ( 2 SELECT * 3 FROM partition 4 WHERE ST_Contains( 5 bounds, -- The bounding box geometry 6 ST_SetSRID(ST_MakePoint(67.861345816761713, 71.91293818642419), 4326) -- The input point 7 ) 8) 9SELECT * 10FROM possible_partitions 11WHERE ST_Contains( 12 polygon, -- The actual polygon geometry 13 ST_SetSRID(ST_MakePoint(67.861345816761713, 71.91293818642419), 4326) -- The input point 14); 15

(When idx exists only on bounds column)


Directly using index on Polygon column

1explain analyze 2 SELECT * 3 FROM partition 4 WHERE ST_Contains( 5 polygon, -- The bounding box geometry 6 ST_SetSRID(ST_MakePoint(63.861345816761713, 25.91293818642419), 4326) -- The input point 7 ) 8)


Avg statistics of using Gist index on Polygon column:

Avg statistics of using Gist index on Bounds column: