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.28 rows=37 width=4)
Recheck Cond: (fts @@ '''test1000'''::tsquery)
-> Bitmap Index Scan on fts_id (cost=0.00..20.28 rows=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