From: "Bernard Dhooghe" <dhoogheb@yahoo.com>
Subject: >= forces row compare and not index elements compare when possible
Date: 9 Jan 2006 09:10:02 -0800
Organization: http://groups.google.com
Lines: 71
Message-ID: <1136826602.613297.42010@f14g2000cwb.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=194.78.88.169;
	posting-account=dAC2RA0AAABP3w9IS1tT7yDUke-pPYRn
To: pgsql-performance@postgresql.org

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