why optimizer not choosing correct index (btree vs btree_gin)

Поиск
Список
Период
Сортировка
От srinivas oguri
Тема why optimizer not choosing correct index (btree vs btree_gin)
Дата
Msg-id CADfH0ysMZcJXv37o1RTTeXcvm-mJ+ehFjccR4iHaSHsLnvS41A@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC inpg_wal "No space left on device"
Следующее
От: Rui DeSousa
Дата:
Сообщение: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC inpg_wal "No space left on device"