Horton

Re: index-only scans

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>

View thread
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org
Date: 2011-08-12T20:03:08Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Reduce the alignment requirement of type "name" from int to char, and arrange

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