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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
