Re: index-only quals vs. security_barrier views

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