On 2012-02-09 18:02, Robert Haas wrote:
> I don't have any appetite for trying to do anything more with
> index-only scans for 9.2, though maybe someone else will think
> otherwise. But I would like very much to get KaiGai's leakproof stuff
> committed, and so it seems like a good idea to reconcile the needs of
> that machinery with what might eventually be needed here.
Those were a couple of nice cases where index-only-scans
could win more than they does today. I have another one here:
2012-02-09 19:17:28.788 jk=# \d testtable Table "public.testtable" Column | Type |
Modifiers
--------+----------+-------------------------------------------------------- id | integer | not null default
nextval('testtable_id_seq'::regclass)fts | tsvector |
Indexes: "prk_idx" UNIQUE, btree (id) "fts_id" gin (fts)
2012-02-09 19:19:39.054 jk=# explain select id from testtable where fts
@@ to_tsquery('english','test1000'); QUERY PLAN
----------------------------------------------------------------------- Bitmap Heap Scan on testtable
(cost=20.29..161.28rows=37 width=4) Recheck Cond: (fts @@ '''test1000'''::tsquery) -> Bitmap Index Scan on fts_id
(cost=0.00..20.28rows=37 width=0) Index Cond: (fts @@ '''test1000'''::tsquery)
(4 rows)
Time: 0.494 ms
2012-02-09 19:19:52.748 jk=#
In this situation the tuple can be regenerated from the index, but
not from the index-satisfying the where clause, this allows significantly
more complex where-clauses and may also benefit situations where
we only going for one or more of the primary-key/foreing-key columns
for join-conditions.
Above situation does not need to involve a gin-index, but a btree index
where the where clause can be matched up using one index, and the tuple
constructed using another falls into the same category.
--
Jesper