need your expert opinion on index usage and query planner behaviour.
\d orders Partitioned table "public.orders" Column | Type | Collation | Nullable | Default -----------------------------+--------------------------+-----------+----------+---------------------------------------------- oid | character varying | | not null | user_id | character varying | | not null | tags | text[] | | not null | category | character varying | | | description | character varying | | | order_timestamp | timestamp with time zone | | not null | ..... Partition key: RANGE (order_timestamp) Indexes: "orders_uid_country_ot_idx" btree (user_id, country, order_timestamp) "orders_uid_country_cat_ot_idx" btree (user_id, country, category, order_timestamp desc) "orders_uid_country_tag_gin_idx" gin (user_id, country, tags) WITH (fastupdate=off) "orders_uid_oid_ot_key" UNIQUE CONSTRAINT, btree (user_id, oid, order_timestamp) I have observed the following behaviour based on query param when I run the following query, select * from orders where user_id = 'u1' and country = 'c1' and tags && '{t1}' and order_timestamp >= '2021-01-01 00:00:00+00' and order_timestamp < '2021-03-25 05:45:47+00' order by order_timestamp desc limit 10 offset 0
case 1: for records with t1 tags where t1 tags occupies 99% of the records for user u1, 1st index orders_uid_country_ot_idx is picked up.
Limit (cost=0.70..88.97 rows=21 width=712) (actual time=1.967..12.608 rows=21 loops=1) -> Index Scan Backward using orders_y2021_jan_to_uid_country_ot_idx on orders_y2021_jan_to_jun orders (cost=0.70..1232.35 rows=293 width=712) (actual time=1.966..12.604 rows=21 loops=1) Index Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'c1'::text) AND (order_timestamp >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (order_timestamp < '2021-03-25 05:45:47+00'::timestamp with time zone)) Filter: (tags && '{t1}'::text[]) Planning Time: 0.194 ms Execution Time: 12.628 ms case 2: But when I query for tags value t2 with something like tags && '{t2}' and it is present in 0 to <3% of records for a user, gin index is picked up.
Limit (cost=108.36..108.38 rows=7 width=712) (actual time=37.822..37.824 rows=0 loops=1) -> Sort (cost=108.36..108.38 rows=7 width=712) (actual time=37.820..37.821 rows=0 loops=1) Sort Key: orders.order_timestamp DESC Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on orders_y2021_jan_to_jun orders (cost=76.10..108.26 rows=7 width=712) (actual time=37.815..37.816 rows=0 loops=1) Recheck Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'ID'::text) AND (tags && '{t2}'::text[])) Filter: ((order_timestamp >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (order_timestamp < '2021-03-25 05:45:47+00'::timestamp with time zone)) -> Bitmap Index Scan on orders_y2021_jan_to_uid_country_tag_gin_idx (cost=0.00..76.10 rows=8 width=0) (actual time=37.812..37.812 rows=0 loops=1) Index Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'c1'::text) AND (tags && '{t2}'::text[])) Planning Time: 0.190 ms Execution Time: 37.935 ms -
Is this because the query planner identifies that since 99% of the records is covered in case 1, it skips the gin index and directly uses the 1st index? If so, does postgres identifies it based on the stats?
-
Before gin index creation, when 1st index is picked for case 2, performance was very bad since index access range is high. i.e number of records that satisfies the condition of user id, country and time column is very high. gin index improved it but i’m curious to understand how postgres chooses it selectively.
-
orders_uid_country_cat_ot_idxwas added to support filter by category since when gin index was used when filtered by just category or by both category and tags, the performance was bad compared to when the btree index ofuser_id, country, category, order_timestampis picked up . I expectedginindex to work well for all the combination of category and tags filter. What could be the reason? The table contains millions of rows
