I create a table with 43kk rows, populate them with values 1..200. So ~220k per each number spreaded through the table.

create table foo (id integer primary key, val bigint);
insert into foo ;
select i, random() * 200 from generate_series(1, 43000000) as i;
create index val_index on foo(val);
vacuum analyze foo;
explain analyze select id from foo where val = 55;

Result: at depesz

I except total runtime < 1s, is it possible? I have SSD, core i5 (1,8), 4gb RAM. 9,3 Postgres.

If I use Index Only scan it works very fast:

explain analyze select val from foo where val = 55;

at depesz But I need to select id not val so Incex Only scan is not suitable in my case. Thanks in advance!

asked Oct 31 '14 at 05:26

user1337's gravatar image

user1337
6114

edited Oct 31 '14 at 10:45

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

why my question is empty?

(Oct 31 '14 at 05:28) user1337

There seems to be a bug with links. I've styled the links a little bit until the bug is not triggered anymore. Sorry for that.

(Oct 31 '14 at 08:54) Markus Winand ♦♦

So, what's your question. Index-Only scan is supposed to be fast. Have you read this: Index-Only Scan

(Oct 31 '14 at 08:56) Markus Winand ♦♦

Thanks, works now. Yes, 'Index Only' works well. But I need select another field - not index and there are complicates. Select by index (both Bitmap and plain Index scans) works even slower then Sequential scan.

(Oct 31 '14 at 09:12) user1337

I edited my question and it is gone again. I made a shorter version - it is clear now what I'm asking.

(Oct 31 '14 at 10:23) user1337

One Answer:

But I need to select id not val so Incex Only scan is not suitable in my case. Thanks in advance!

Index only scan is always possible, the trick is to make sure all required information is in the index. E.g. try using this index:

create index val_id_index on foo(val, id);

answered Oct 31 '14 at 10:46

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

Brilliant, works! Btw, it can't work in Postgres with verison < 9.2, right?

(Oct 31 '14 at 11:23) user1337

No, Index-Only-Scan was added with PostgreSQL 9.2

(Oct 31 '14 at 11:31) Markus Winand ♦♦