[YSQL] Optimizer is choosing different plans for 2 queries which use partition columns in different syntaxes

[KavyaShivashankar]

Jira Link: DB-4138

Description

I have 2 queries using all partition keys effectively doing the same thing with different syntaxes. The execution plan for both these are different ( one uses a filter while the other one does not). Why is parsing phase able to parse 2 PK columns in the same way for both the queries, but differs for one PK column between the 2 queries.?

Cluster is a single region multi-AZ cluster

Scenario 1 - default seqscan chosen for query 1

yugabyte=# show enable_seqscan;
 enable_seqscan
----------------
 on
(1 row)

yugabyte=# explain analyse select * from test where (id,type,version) IN (('11111','A','1.1'),('11112','A','1.1'));;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..110.00 rows=1000 width=138) (actual time=1.256..5.028 rows=2 loops=1)
   Filter: ((type = 'A'::text) AND (version = '1.1'::text) AND ((id = '11111'::text) OR (id = '11112'::text)))
   Rows Removed by Filter: 3
 Planning Time: 0.114 ms
 Execution Time: 5.086 ms
 Peak Memory Usage: 8 kB
(6 rows)

yugabyte=# explain analyze select * from test where id IN ('11111','11112') and type = 'A' and version = '1.1';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.00..4.12 rows=1 width=138) (actual time=2.190..2.193 rows=2 loops=1)
   Index Cond: ((id = ANY ('{11111,11112}'::text[])) AND (type = 'A'::text) AND (version = '1.1'::text))
 Planning Time: 0.104 ms
 Execution Time: 2.247 ms
 Peak Memory Usage: 8 kB
(5 rows)

Scenario 2 - seqcan turned off. Query 1 choses index on columns type and version and adds a filter on column id instead of choosing index for all the 3 columns like query 2

yugabyte=# set enable_seqscan=false;
SET

yugabyte=# explain analyse select * from test where (id,type,version) IN (('11111','A','1.1'),('11112','A','1.1'));
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.00..129.00 rows=1000 width=138) (actual time=1.544..5.791 rows=2 loops=1)
   Index Cond: ((type = 'A'::text) AND (version = '1.1'::text))
   Filter: ((id = '11111'::text) OR (id = '11112'::text))
   Rows Removed by Filter: 3
 Planning Time: 0.157 ms
 Execution Time: 5.850 ms
 Peak Memory Usage: 8 kB
(7 rows)

yugabyte=# explain analyze select * from test where id IN ('11111','11112') and type = 'A' and version = '1.1';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.00..4.12 rows=1 width=138) (actual time=1.574..1.579 rows=2 loops=1)
   Index Cond: ((id = ANY ('{11111,11112}'::text[])) AND (type = 'A'::text) AND (version = '1.1'::text))
 Planning Time: 0.104 ms
 Execution Time: 1.632 ms
 Peak Memory Usage: 8 kB
(5 rows)

Tried these queries on vanilla Postgres 11.2 docker image and here is the plan

create table test(
  id text,
  type text,
  version text,
  value bytea,
  entity_ts timestamp without time zone,
  cache_version smallint,
primary key(id, type, version));

postgres=# explain analyse select * from test where (id,type,version) IN (('11111','A','1.1'),('11112','A','1.1'));;
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=8.32..12.34 rows=1 width=138) (actual time=0.151..0.152 rows=2 loops=1)
   Recheck Cond: (((id = '11111'::text) AND (type = 'A'::text) AND (version = '1.1'::text)) OR ((id = '11112'::text) AND (type = 'A'::text) AND (version = '1.1'::text)))
   Heap Blocks: exact=1
   ->  BitmapOr  (cost=8.32..8.32 rows=1 width=0) (actual time=0.113..0.114 rows=0 loops=1)
         ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.16 rows=1 width=0) (actual time=0.110..0.110 rows=1 loops=1)
               Index Cond: ((id = '11111'::text) AND (type = 'A'::text) AND (version = '1.1'::text))
         ->  Bitmap Index Scan on test_pkey  (cost=0.00..4.16 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
               Index Cond: ((id = '11112'::text) AND (type = 'A'::text) AND (version = '1.1'::text))
 Planning Time: 0.575 ms
 Execution Time: 0.341 ms
(10 rows)

postgres=# explain analyze select * from test where id IN ('11111','11112') and type = 'A' and version = '1.1';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.15..12.33 rows=1 width=138) (actual time=0.090..0.097 rows=2 loops=1)
   Index Cond: ((id = ANY ('{11111,11112}'::text[])) AND (type = 'A'::text) AND (version = '1.1'::text))
 Planning Time: 0.446 ms
 Execution Time: 0.135 ms
(4 rows)

[andrei-mart]

We have been receiving requests to support bitmap scans: #4634, #10848