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
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 |