query planner does not canonicalize infix operators

From: Will Leinweber <will(at)heroku(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: query planner does not canonicalize infix operators
Date: 2012-03-06 00:26:31
Message-ID: CAL8LqZSN4bOMouRNcRHWkX4F7osNF77OJ5BCus-vp-NFaEXo=g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I created an index on an hstore function, fetchval(hstore, text), however
when I use the -> infix operator which resolves to the very same function,
this index is not used. It should be used.

I have included an example:

Table with hstore index:

de10keipt01939=> \d log_data
Table "public.log_data"
Column | Type | Modifiers

--------+--------------------------+-------------------------------------------------------
id | bigint | not null default
nextval('log_data_id_seq'::regclass)
time | timestamp with time zone |
data | hstore |
Indexes:
"index_log_data_by_time" btree ("time")
"index_participant_id" btree (fetchval(data, 'participant_id'::text))

query with function notation:

de10keipt01939=> explain ANALYZE select * from log_data where
(data->'participant_id')='2851' order by id desc;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------
Sort (cost=16432.56..16433.36 rows=1583 width=315) (actual
time=198.643..198.777 rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
-> Seq Scan on log_data (cost=0.00..16415.74 rows=1583 width=315)
(actual time=6.926..198.297 rows=183 loops=1)
Filter: ((data -> 'participant_id'::text) = '2851'::text)
Total runtime: 198.922 ms
(6 rows)

query with infix notation:

de10keipt01939=> explain ANALYZE select * from log_data where
fetchval(data,'participant_id')='2851' order by id desc;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=341.14..341.23 rows=179 width=315) (actual time=0.724..0.841
rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
-> Bitmap Heap Scan on log_data (cost=2.35..339.80 rows=179 width=315)
(actual time=0.091..0.489 rows=183 loops=1)
Recheck Cond: (fetchval(data, 'participant_id'::text) =
'2851'::text)
-> Bitmap Index Scan on index_participant_id (cost=0.00..2.34
rows=179 width=0) (actual time=0.060..0.060 rows=183 loops=1)
Index Cond: (fetchval(data, 'participant_id'::text) =
'2851'::text)
Total runtime: 1.010 ms
(8 rows)

—Will

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2012-03-06 00:37:02 Dropping PL language retains support functions
Previous Message Josh Berkus 2012-03-05 23:29:47 Re: Checksums, state of play