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