Hello experts,
I have a table with below structure.
Table "public.work_units"
Column | Type | Modifiers
---------------------------------+--------------------------+-----------
col1 | integer | not null
col2 | character varying(20) | not null
col3 | integer | not null
col4 | integer | not null
col5 | character varying(100) | not null
col6 | tstzrange | not null
col7 | timestamp with time zone | not null
col8 | integer[] | not null
col9 | jsonb | not null
col10 | jsonb | not null
col1 | integer | not null
col2 | timestamp with time zone | not null
Indexes:
"work_units_natural_key_index" btree (col1, col2, col3, col5)
"work_units_search_btree_idx" btree (col1, col2, col4, col8)
"work_units_search_index" gin (col1, col2, col4, col8)
"work_units_unique" EXCLUDE USING gist (col1 WITH =, col2 WITH =, col3 WITH =, col5 WITH =, col6 WITH &&)
When I execute the query with index "work_units_search_btree_idx" it is taking less time compare to the default index choosen by optimizer "work_units_search_index" (btree_gin). Why PostgreSQL is not picking the btree index and why it is foing for btree_gin which is taking more time.
iris=> explain analyze
/*+ Indexscan(w work_units_search_btree_idx) */
select count(*) from "test" AS s
, "work_units" AS w
WHERE s."col1" = w."col1"
AND s."col2" = w."col2"
AND s."col3" = w."col3"
AND s."col5" = w."col5"
AND s."col4" = w."col4"
AND s."col8" = w."col8"
AND s."col11" = w."col11"
AND w."col6" @> lower(s."col6");
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=14372.00..14372.01 rows=1 width=8) (actual time=51.291..51.292 rows=1 loops=1)
-> Nested Loop (cost=0.42..14372.00 rows=1 width=0) (actual time=2.972..50.133 rows=4000 loops=1)
-> Seq Scan on test s (cost=0.00..324.00 rows=4000 width=128) (actual time=2.919..9.296 rows=4000 loops=1)
-> Index Scan using work_units_search_btree_idx on work_units w (cost=0.42..3.50 rows=1 width=110) (actual time=0.008..0.009 rows=1 loops=4000)
Index Cond: ((col1 = s.col1) AND ((col2)::text = (s.col2)::text) AND (col4 = s.col4) AND (col8 = s.col8))
Filter: ((s.col3 = col3) AND ((s.col5)::text = (col5)::text) AND (s.col11 = col11) AND (col6 @> lower(s.col6)))
Rows Removed by Filter: 0
Planning time: 2.782 ms
Execution time: 51.390 ms
(9 rows)
iris=> explain analyze select count(*)
FROM "test" AS s
, "work_units" AS w
WHERE s."col1" = w."col1"
AND s."col2" = w."col2"
AND s."col3" = w."col3"
AND s."col5" = w."col5"
AND s."col4" = w."col4"
AND s."col8" = w."col8"
AND s."col11" = w."col11"
AND w."col6" @> lower(s."col6");
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10541.37..10541.38 rows=1 width=8) (actual time=10467.860..10467.860 rows=1 loops=1)
-> Nested Loop (cost=0.51..10541.37 rows=1 width=0) (actual time=2.595..10465.596 rows=4000 loops=1)
-> Seq Scan on test s (cost=0.00..324.00 rows=4000 width=128) (actual time=0.005..2.451 rows=4000 loops=1)
-> Bitmap Heap Scan on work_units w (cost=0.51..2.54 rows=1 width=110) (actual time=2.612..2.613 rows=1 loops=4000)
Recheck Cond: ((col1 = s.col1) AND ((col2)::text = (s.col2)::text) AND (col4 = s.col4) AND (col8 = s.col8))
Rows Removed by Index Recheck: 0
Filter: ((s.col3 = col3) AND ((s.col5)::text = (col5)::text) AND (s.col11 = col11) AND (col6 @> lower(s.col6)))
Rows Removed by Filter: 0
Heap Blocks: exact=5296
-> Bitmap Index Scan on work_units_search_index (cost=0.00..0.51 rows=1 width=0) (actual time=2.605..2.605 rows=1 loops=4000)
Index Cond: ((col1 = s.col1) AND ((col2)::text = (s.col2)::text) AND (col4 = s.col4) AND (col8 = s.col8))
Planning time: 1.367 ms
Execution time: 10468.157 ms
(13 rows)
Thanks
Srinivas
