Query Analysis for Hub Partition

Inserting 10k rows into partition table for query analysis

explain analyze SELECT *
FROM partition
WHERE 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

 WITH possible_partitions AS (
    SELECT *
    FROM partition
    WHERE ST_Contains(
        bounds,  -- The bounding box geometry
        ST_SetSRID(ST_MakePoint(67.861345816761713, 71.91293818642419), 4326)  -- The input point
    )
)
SELECT *
FROM possible_partitions
WHERE ST_Contains(
    polygon,  -- The actual polygon geometry
    ST_SetSRID(ST_MakePoint(67.861345816761713, 71.91293818642419), 4326)  -- The input point
);

(When idx exists only on bounds column)

Directly using index on Polygon column

explain analyze 
    SELECT *
    FROM partition
    WHERE ST_Contains(
        polygon,  -- The bounding box geometry
        ST_SetSRID(ST_MakePoint(63.861345816761713, 25.91293818642419), 4326)  -- The input point
    )
)

Avg statistics of using Gist index on Polygon column:

Avg statistics of using Gist index on Bounds column: