Re: index-only quals vs. security_barrier views

On Thu, Feb 9, 2012 at 1:33 PM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
> 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.

I don't understand what you're saying here.

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

That doesn't make sense to me. If you probe index A for rows where a
= 1 and find that CTID (100,1) is such a row, and now want to return a
column value b that is not present in that index, the fastest way to
get the row is going to be to fetch block 100 from the heap and return
the data out of the first tuple. To get the value out of some other
index that does include column b would require scanning the entire
index looking for that CTID, just so you could then grab the
corresponding index tuple, which wouldn't make any sense at all.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company