Suppose a table with structure:
Table "public.t4"
Column | Type | Modifiers
--------+---------------+-----------
c1 | character(10) | not null
c2 | character(6) | not null
c3 | date | not null
c4 | character(30) |
c5 | numeric(10,2) | not null
Indexes:
"t4_prim" PRIMARY KEY, btree (c1, c2, c3)
Then 2 queries
echo "explain select * from t4 where (c1,c2,c3) >=
('A','B','1990-01-01') order by c1,c2,c3"|psql test
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using t4_prim on t4 (cost=0.00..54.69 rows=740 width=75)
Filter: (ROW(c1, c2, c3) >= ROW('A'::bpchar, 'B'::bpchar,
'1990-01-01'::date))
(2 rows)
and
echo "explain select * from t4 where (c1,c2,c3) >=
('A','B','1990-01-01') orde>
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using t4_prim on t4 (cost=0.00..54.69 rows=740 width=75)
Filter: (ROW(c1, c2, c3) >= ROW('A'::bpchar, 'B'::bpchar,
'1990-01-01'::date))
(2 rows)
So switching from (c1,c2,c3) compare from = to >= makes the optimizer
see the where clause as a row filter, which is not really the case.
Further
echo "explain select * from t4 where (c1,c2) = ('A','B') order by
c1,c2,c3"|ps>
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t4_prim on t4 (cost=0.00..4.83 rows=1 width=75)
Index Cond: ((c1 = 'A'::bpchar) AND (c2 = 'B'::bpchar))
(2 rows)
here again the index can be used (again), the row count can be greater
than one.
but
echo "explain select * from t4 where (c1,c2) >= ('A','B') order by
c1,c2,c3"|p>
QUERY PLAN
----------------------------------------------------------------------
Index Scan using t4_prim on t4 (cost=0.00..52.84 rows=740 width=75)
Filter: (ROW(c1, c2) >= ROW('A'::bpchar, 'B'::bpchar))
(2 rows)
So >= (or <=) is not optimized against an index where it could be.
Bernard Dhooghe
