why optimizer not choosing correct index (btree vs btree_gin)

From: srinivas oguri <srinivasoguri7(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: why optimizer not choosing correct index (btree vs btree_gin)
Date: 2018-11-16 10:56:25
Message-ID: CADfH0ysMZcJXv37o1RTTeXcvm-mJ+ehFjccR4iHaSHsLnvS41A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Rui DeSousa 2018-11-16 15:29:07 Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC in pg_wal "No space left on device"
Previous Message Achilleas Mantzios 2018-11-16 08:18:07 Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC in pg_wal "No space left on device"