-1

I have a large table with a btree index on multiple keys. If a make a query by fixing the first two columns of the index and putting a unilateral bound on the third column, it results in a very very slow query even if the number of matching rows is very low. If I put a bilateral bound on the third column the query is instead fast. See code snippet below.

I would expect that postgresql should be able to find quickly a lower bound for an indexed column, but in this case it seems it is not.

Can you give an explanation of why I get this problem? How to fix it?

> select min(minute) from data_minutesample where probe_id = 19 and power = 0 and minute between 0 and 22780323;
   min    
----------
 22780262
(1 row)

Time: 28233.498 ms

> select min(minute) from data_minutesample where probe_id = 19 and power = 0 and minute between 22780000 and 22780323;
   min    
----------
 22780262
(1 row)

Time: 13.946 ms

> \d+ data_minutesample
            Table "public.data_minutesample"
[...]
Indexes:
    "data_minutesample_index_unique" UNIQUE, btree (probe_id, power, minute, proto_id, src_port, dst_port, src_addr, dst_addr)
Emanuele Paolini
  • 9,912
  • 3
  • 38
  • 64
  • 2
    Have you ran `vacuum analyze` after populating the table ? – wildplasser May 26 '13 at 18:20
  • All of this should be in your question without me asking for it: version of PostgreSQL, table definition, cardinality, method of timing (is data transfer included?), output of EXPLAIN ANALYZE, ideally posted to explain.depesz.com. – Erwin Brandstetter May 26 '13 at 21:16

1 Answers1

1

Try adding EXPLAIN to the start of each query so that you can see how the query planner is deciding to execute them.

My guess is that for the first one it is deciding not to use the index, and instead to do a table scan, because of the fact that you are selecting a large range of values. It probably doesn't realise that there is only actually one matching value in that range.

You may find that running ANALYZE on the table to make sure the planner has up to date statistics will help it make better decisions.

TomH
  • 8,900
  • 2
  • 32
  • 30