Обсуждение: >= forces row compare and not index elements compare when possible

От:
"Bernard Dhooghe"
Дата:

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


От:
Tom Lane
Дата:

"Bernard Dhooghe" <> writes:
> So >= (or <=) is not optimized against an index where it could be.

Work in progress...

            regards, tom lane