Re: index-only quals vs. security_barrier views

On 2012-02-09 22:17, Jesper Krogh wrote:
> On 2012-02-09 21:09, Robert Haas wrote:
>> 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.
>>
> You're right, in my head, everything it wired up against my primary
> keys, of-course that isn't the case for the DB. Sorry for the noise.

Ok, but there are still cases where we don't even need to construct
a data tuple at all:

2012-02-11 13:14:01.579 jk=# explain select count(*) from testtable 
where fts @@ to_tsquery('english','test1');                                QUERY PLAN
--------------------------------------------------------------------------- Aggregate  (cost=31.24..31.25 rows=1
width=0)  ->  Bitmap Heap Scan on testtable  (cost=16.03..31.23 rows=4 width=0)         Recheck Cond: (fts @@
'''test1'''::tsquery)        ->  Bitmap Index Scan on ftsid  (cost=0.00..16.03 rows=4 width=0)               Index
Cond:(fts @@ '''test1'''::tsquery)
 
(5 rows)


Another idea sprung into my head, that indices on (ctid,<some mix of 
columns>)
could actually serve as some kind of "vertical" partitioning of the table.

Wether it actually will me more efficient or not need to be tested.

Jesper

-- 
Jesper