Slow Select Query with DATE Between

Question

I searched for my problem a bit, but couldn't find a solution.

I run a PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit and my Query is pretty simple.

EXPLAIN (ANALYZE) SELECT CUSTOMER, PRICE, BUYDATE FROM dbo.Invoice WHERE CUSTOMER = 11111111 AND BUYDATE BETWEEN '2012-11-01' AND '2013-10-31';

Output:

                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on Invoice  (cost=88193.54..152981.03 rows=20699 width=14) (actual time=987.205..987.242 rows=36 loops=1)
   Recheck Cond: ((CUSTOMER = 11111111) AND (BUYDATE >= '2012-11-01'::date) AND (BUYDATE <= '2013-10-31'::date))
   ->  BitmapAnd  (cost=88193.54..88193.54 rows=20699 width=0) (actual time=987.189..987.189 rows=0 loops=1)
         ->  Bitmap Index Scan on ix_Invoice  (cost=0.00..1375.69 rows=74375 width=0) (actual time=0.043..0.043 rows=40 loops=1)
               Index Cond: (CUSTOMER = 11111111)
         ->  Bitmap Index Scan on ix_Invoice3  (cost=0.00..86807.24 rows=4139736 width=0) (actual time=986.562..986.562 rows=4153999 loops=1)
               Index Cond: ((BUYDATE >= '2012-11-01'::date) AND (BUYDATE <= '2013-10-31'::date))
 Total runtime: 987.294 ms
(8 rows)

The Table Structure:

      Column      |           Type            | Modifiers | Storage  | Stats target | Description
-----------------+---------------------------+-----------+----------+--------------+-------------
 profitcenter    | character varying(5)      | not null  | extended |              |
 invoicenumber   | character varying(10)     | not null  | extended |              |
 invoiceposition | character varying(6)      | not null  | extended |              |
 buydate         | date                      | not null  | plain    |              |
 customer        | integer                   |           | plain    |              |
 nettowert       | numeric(18,2)             |           | main     |              |
Indexes:
    "filialbons_key" PRIMARY KEY, btree (profitcenter, invoicenumber, invoiceposition, buydate)
    "ix_Invoice" btree (customer)
    "ix_Invoice2" btree (invoicenumber)
    "ix_Invoice3" btree (buydate)
    "ix_Invoice4" btree (articlenumber)
Has OIDs: no

Example Output from the Query:

customer | price | buydate
--------------+-----------+----
 11111111 |  8.32 | 2013-02-06
 11111111 |  5.82 | 2013-02-06
 11111111 | 16.64 | 2013-02-06

I ran the same Query on a MSSQL 2010? Express with the Date Column as varchar() and it was much faster.

Thanks for your help

Im no postgre expert but it seems that the use of invoice_ix3 is not optimal - without knowing how many rows in the table i'm guessing that a better plan would be to filter by id first and then apply the date filter row by row (instead of bitmap and). try forcing it with select * from (select * from dbo.Invoice WHERE CUSTOMER = 11111111) where BUYDATE BETWEEN '2012-11-01' AND '2013-10-31' — haki
– haki, Commented Nov 14, 2013 at 12:48
I tried that. It's the same output (EXPLAIN ANALYZE) as above :( — Beig
– Beig, Commented Nov 14, 2013 at 13:14

alexius · Accepted Answer · 2016-05-05 18:15:11Z

3

with index on (customer, buydate) query should work much faster.
you may try to help planner to chose better plan by collecting more statistic data:

ALTER TABLE Invoice ALTER COLUMN customer SET STATISTICS 1000;
ALTER TABLE Invoice ALTER COLUMN buydate SET STATISTICS 1000;
ANALYZE Invoice;

edited May 5, 2016 at 18:15

answered Nov 14, 2013 at 14:24

alexius

2,58620 silver badges24 bronze badges

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

2 Comments

Beig Over a year ago

Thanks, I think the ANALYZE table did the work. pastebin.com/LX3Mm9aX the Output from EXPLAIN ANALYZE SELECT.

brauliobo Over a year ago

there should be no equal after statistics

Collectives™ on Stack Overflow

Slow Select Query with DATE Between

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related