Slow Select over View

Question

My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k.

But I still think it should be faster than ~16 seconds?

Table receipts:

     Column      |            Type           | Modifiers | Storage     | Stats target  | Description
-----------------+---------------------------+-----------+-------------+---------------+--------------
 profitcenter    | character varying(5)      | not null  | extended    |               |
 receiptnumber   | bigint                    | not null  | plain       |               |
 receiptposition | character varying(6)      | not null  | extended    |               |
 date            | date                      | not null  | plain       |               |
 customerid      | integer                   |           | plain       |               |
 vkorg           | character varying(4)      |           | extended    |               |
 artikelnummer   | integer                   |           | plain       |               |
 price           | numeric(18,2)             |           | main        |               |
Indexes receiptnumber, date and customerid as B-Tree.

Table cc_add_entry2:

    Column     |          Type          | Modifiers | Storage     | Stats target  | Description
---------------+------------------------+-----------+-------------+---------------+--------------
 receiptnumber | bigint                 | not null  | plain       |               |
 customerid    | bigint                 |           | plain       |               |
 user          | character varying(255) |           | extended    |               |
 note          | text                   |           | extended    |               |
 date          | date                   |           | plain       |               |
Indexes receiptnumber, customerid as B-Tree.

The view is simple:

 SELECT s.receiptnumber,
    s.receiptposition,
    s.date,
    COALESCE(b.customerid, (s.customerid)::bigint) AS customerid,
    s.price
   FROM (dashboard.receipts s
     LEFT JOIN dashboard.cc_add_entry2 b ON ((s.receiptnumber = b.receiptnumber)));

EXPLAIN ANALYZE over a simple query:

explain analyze select * from test2 where customerid = 520001215;

 Hash Left Join  (cost=582.40..1086787.72 rows=100185 width=37) (actual time=2124.048..15937.744 rows=52 loops=1)
   Hash Cond: (s.receiptnumber = b.receiptnumber)
   Filter: (COALESCE(b.customerid, (s.customerid)::bigint) = 520001215)
   Rows Removed by Filter: 20036943
   ->  Seq Scan on receipts s  (cost=0.00..599969.96 rows=20036996 width=29) (actual time=0.904..7187.563 rows=20036995 loops=1)
   ->  Hash  (cost=360.51..360.51 rows=17751 width=16) (actual time=5.850..5.850 rows=17751 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 694kB
         ->  Seq Scan on cc_add_entry2 b  (cost=0.00..360.51 rows=17751 width=16) (actual time=0.005..2.537 rows=17751 l
oops=1)
 Total runtime: 15937.962 ms

Answer 1

I am pretty sure the reason is that customerid in the view is the result of:

COALESCE(b.customerid, s.customerid::bigint)

After LEFT JOIN, cast & COALESCE, the predicate cannot be pushed down, so both tables have to be read in full, before the filter customerid = 520001215 can be applied. The fact that customerid can be NULL in both tables is not helping either. Can at least one be set to NOT NULL?

Extremely inefficient.

Run this query, and compare performance:

SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  c.customerid
    ,  r.price
FROM   dashboard.cc_add_entry2 c 
JOIN   dashboard.receipts r USING (receiptnumber)
WHERE  c.customerid = 520001215;

UNION ALL
SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  r.customerid::bigint
    ,  r.price
FROM   dashboard.receipts r
LEFT   JOIN dashboard.cc_add_entry2 c USING (receiptnumber)
WHERE  r.customerid = 520001215
AND    c.customerid IS NULL;

Should be much faster. If you can't wrap it into a view, either use this query instead or write a SQL or plpgsql function that takes customerid as parameter.

Also these multicolumn indices would help some more:

CREATE INDEX foo1 ON dashboard.cc_add_entry2 (customerid, receiptnumber);
CREATE INDEX foo2 ON dashboard.cc_add_entry2 (receiptnumber)
WHERE customerid IS NULL;
CREATE INDEX foo3 ON dashboard.receipts (customerid, receiptnumber);

Answer 2

Do you have clustered and nonclustered indexes? Try clustered index on receipt number and date. And non clustered index on receipt number, receipt position, date, and price