From: Bernard Dhooghe <dhoogheb@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: >= forces row compare and not index elements compare when possible
Date: 9 Jan 2006 09:10:02 -0800
Message-ID: <1136826602.613297.42010@f14g2000cwb.googlegroups.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dhoogheb@yahoo.com
Subject: Re: >= forces row compare and not index elements compare when possible
In-Reply-To: <1136826602.613297.42010@f14g2000cwb.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox