PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

Question

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: http://explain.depesz.com/s/fdsm

I expect 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;

http://explain.depesz.com/s/7hm

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

Thanks in advance!

Additional info:

SELECT relname, relpages, reltuples::numeric, pg_size_pretty(pg_table_size(oid)) 
FROM pg_class WHERE oid='foo'::regclass;

Result:

"foo";236758;43800000;"1850 MB"

Config:

"cpu_index_tuple_cost";"0.005";""
"cpu_operator_cost";"0.0025";""
"cpu_tuple_cost";"0.01";""
"effective_cache_size";"16384";"8kB"
"max_connections";"100";""
"max_stack_depth";"2048";"kB"
"random_page_cost";"4";""
"seq_page_cost";"1";""
"shared_buffers";"16384";"8kB"
"temp_buffers";"1024";"8kB"
"work_mem";"204800";"kB"

Could you include the output of this query in the question: SELECT relname,relpages,reltuples::numeric,pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE oid='foo'::regclass; — vyegorov
– vyegorov, Commented Oct 31, 2014 at 9:40
Please, do EXPLAIN (analyze, buffers) for both of your queries. And include output of this query: SELECT name,setting,unit FROM pg_settings WHERE source NOT IN ('default','override') UNION ALL SELECT 'version',version(),NULL; — vyegorov
– vyegorov, Commented Oct 31, 2014 at 9:54
added config from query you provided and updated links to explains (contains buffers information now) — fasth
– fasth, Commented Oct 31, 2014 at 10:42

fasth · Accepted Answer · 2014-10-31 10:52:39Z

14

I have got answer here: http://ask.use-the-index-luke.com/questions/235/postgresql-bitmap-heap-scan-on-index-is-very-slow-but-index-only-scan-is-fast

The trick is to use composite index for id and value:

create index val_id_index on foo(val, id);

So Index Only scan will be used, but I can select id now.

select id from foo where val = 55;

Result:

http://explain.depesz.com/s/nDt3

But this works ONLY in Postgres with version 9.2+. If you have forced to use versions below try another options.

edited Oct 31, 2014 at 10:52

answered Oct 31, 2014 at 10:47

fasth

2,3627 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vyegorov · Accepted Answer · 2014-10-31 11:12:30Z

Although you're querying only 0,5% of the table, or ~10MB worth of data (out of nearly 2GB table), values of interest are spread evenly across whole table.

You can see it in the first plan you've provided:

BitmapIndexScan completes in 123.172ms
BitmapHeapScan takes 17055.046ms.

You can try clustering your tables based on index order, which will put rows together on the same pages. On my SATA disks I have the following:

SET work_mem TO '300MB';
EXPLAIN (analyze,buffers) SELECT id FROM foo WHERE val = 55;

  Bitmap Heap Scan on foo  (...) (actual time=90.315..35091.665 rows=215022 loops=1)
    Heap Blocks: exact=140489
    Buffers: shared hit=20775 read=120306 written=24124

SET maintenance_work_mem TO '1GB';
CLUSTER foo USING val_index;
EXPLAIN (analyze,buffers) SELECT id FROM foo WHERE val = 55;

  Bitmap Heap Scan on foo  (...) (actual time=49.215..407.505 rows=215022 loops=1)
    Heap Blocks: exact=1163
    Buffers: shared read=1755

Of course, this is a one-time operation and it'll get longer bit-by-bit over the time.

Pavel Stehule · Accepted Answer · 2014-10-31 07:01:00Z

1

You can try to reduce random_page_cost -- for SSD it can be 1. Second, you can increase a work_mem .. 10MB is relatively low value for current servers with gigabytes RAM. You should to recheck effective_cache_size - it can be too low too.

work_mem * max_connection * 2 + shared_buffers < RAM dedicated for Postgres
effective_cache ~ shared_buffers + file system cache

answered Oct 31, 2014 at 7:01

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

3 Comments

fasth Over a year ago

Set effective_cache_size to 128mb and work_mem to 200mb and it didn't help, random_page_cost now 1.

Pavel Stehule Over a year ago

you can try compose index. You can try to penalize row operation by increasing cpu_tuple_cost. You have to find a combination of work_mem, cpu_tuple_cost, random_page_cost, seq_page_cost that works well for your system.

fasth Over a year ago

what compose index are you suggesting? I filter only by single field so composite index is nonsence, isn't it?

Collectives™ on Stack Overflow

PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related