From: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: index-only scans |
Date: | 2011-08-12 20:03:08 |
Message-ID: | 4E4586FC.6050107@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 11.08.2011 23:06, Robert Haas wrote:
> Comments, testing, review appreciated...
I would've expected this to use an index-only scan:
postgres=# CREATE TABLE foo AS SELECT generate_series(1,100000) AS id;
SELECT 100000
postgres=# CREATE INDEX i_foo ON foo (id) WHERE id = 10;
CREATE INDEX
postgres=# VACUUM ANALYZE foo;
VACUUM
postgres=# EXPLAIN SELECT id FROM foo WHERE id = 10;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using i_foo on foo (cost=0.00..8.27 rows=1 width=4)
Index Cond: (id = 10)
(2 rows)
If it's not a predicate index, then it works:
postgres=# DROP INDEX i_foo;
DROP INDEX
postgres=# EXPLAIN SELECT id FROM foo WHERE id = 10;
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using i_foo2 on foo (cost=0.00..8.28 rows=1 width=4)
Index Cond: (id = 10)
(2 rows)
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2011-08-12 20:03:56 | Re: VACUUM FULL versus system catalog cache invalidation |
Previous Message | Florian Pflug | 2011-08-12 19:57:11 | Re: Inserting heap tuples in bulk in COPY |