pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed>= forces row compare and not index elements compare when possible
2+ messages / 2 participants
[nested] [flat]
* >= forces row compare and not index elements compare when possible
@ 2006-01-09 17:10 Bernard Dhooghe <dhoogheb@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bernard Dhooghe @ 2006-01-09 17:10 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: >= forces row compare and not index elements compare when possible
@ 2006-01-14 18:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bernard Dhooghe <dhoogheb@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-14 18:10 UTC (permalink / raw)
To: Bernard Dhooghe <dhoogheb@yahoo.com>; +Cc: pgsql-performance
"Bernard Dhooghe" <dhoogheb@yahoo.com> writes:
> So >= (or <=) is not optimized against an index where it could be.
Work in progress...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-14 18:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-09 17:10 >= forces row compare and not index elements compare when possible Bernard Dhooghe <dhoogheb@yahoo.com>
2006-01-14 18:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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