Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a query that takes about 8 minutes to execute if I want one day's scans, and we're going to need months'.
It retrieves some columns which are used to determine to which bracket a scanned product belongs.

Tables:

Table "public.barcode_log"
                Column                |           Type           | Modifiers | Storage  | Description
--------------------------------------+--------------------------+-----------+----------+-------------
 barcode_log_id                       | text                     | not null  | extended |
 scan_time                            | timestamp with time zone |           | plain    |
 amazon_item_auk_sales_rank           | numeric(20,0)            |           | main     |
 offering_price_value                 | numeric(18,2)            |           | main     |
Indexes:
    "pk_barcode_log" PRIMARY KEY, btree (barcode_log_id)
    "bl_aiasr_idx" btree (amazon_item_auk_sales_rank)
    "bl_st_idx" btree (scan_time)

 Table "public.order_header"
            Column            |           Type           | Modifiers | Storage  | Description
------------------------------+--------------------------+-----------+----------+-------------
 order_id                     | text                     | not null  | extended |
 status_id                    | text                     |           | extended |
Indexes:
    "pk_order_header" PRIMARY KEY, btree (order_id)
    "order_hdr_stts" btree (status_id)

Table "public.order_item"
            Column            |           Type           | Modifiers | Storage  | Description
------------------------------+--------------------------+-----------+----------+-------------
 order_id                     | text                     | not null  | extended |
 order_item_seq_id            | text                     | not null  | extended |
 product_id                   | text                     |           | extended |
Indexes:
    "pk_order_item" PRIMARY KEY, btree (order_id, order_item_seq_id)
    "order_item_product" btree (product_id)


Table "public.order_item_attribute"
        Column         |           Type           | Modifiers | Storage  | Description
-----------------------+--------------------------+-----------+----------+-------------
 order_id              | text                     | not null  | extended |
 order_item_seq_id     | text                     | not null  | extended |
 attr_name             | text                     | not null  | extended |
 attr_value            | text                     |           | extended |
Indexes:
    "pk_order_item_attribute" PRIMARY KEY, btree (order_id, order_item_seq_id, attr_name)
    "order_item_attr" btree (order_id, order_item_seq_id)

Table "public.product"
            Column            |           Type           | Modifiers | Storage  | Description
------------------------------+--------------------------+-----------+----------+-------------
 product_id                   | text                     | not null  | extended |
 mb_type_enum                 | text                     |           | extended |
Indexes:
    "pk_product" PRIMARY KEY, btree (product_id)
    "prod_mb_type_enum" btree (mb_type_enum)

I'm not sure how many rows should I get back. There are ~22M barcode scans, and the query should only return those that have one of the listed status ids. Probably a fraction of the total.

How could I optimise this query to run better?
The postgres server version is 9.1.13.

The query:

SELECT bl.amazon_item_auk_sales_rank AS salesRank, p.mb_type_enum AS itemType, p.product_id AS productId,
bl.offering_price_value AS offeredPrice
FROM order_header oh
INNER JOIN order_item AS oi ON oh.order_id = oi.order_id
INNER JOIN product AS p ON oi.product_id = p.product_id
INNER JOIN order_item_attribute AS oia ON oi.order_id = oia.order_id and oi.order_item_seq_id = oia.order_item_seq_id and oia.attr_name = 'barcodeLogId'
INNER JOIN barcode_log AS bl ON bl.barcode_log_id = oia.attr_value
WHERE oh.status_id IN ('ORDER_SUBMITTED','ORDER_PARTIALLY_RECEIVED','ORDER_ACCEPTED','ORDER_COMPLETED','ORDER_REJECTED','ORDER_EXPIRED')
AND bl.scan_time >= '2014-06-25 12:00:00.000+00' AND bl.scan_time <= '2014-06-26 12:00:00.000+00';

Explain analyze:

Nested Loop  (cost=419168.86..791679.51 rows=1919 width=28) (actual time=69820.020..82783.043 rows=2590 loops=1)
   ->  Nested Loop  (cost=419168.86..780224.12 rows=1919 width=17) (actual time=69819.905..71149.468 rows=2590 loops=1)
         ->  Hash Join  (cost=419168.86..736808.40 rows=6035 width=29) (actual time=69792.970..70650.651 rows=2590 loops=1)
               Hash Cond: (oia.order_id = oh.order_id)
               ->  Hash Join  (cost=410741.13..728119.13 rows=22994 width=23) (actual time=69495.839..70328.934 rows=6103 loops=1)
                     Hash Cond: (oia.attr_value = bl.barcode_log_id)
                     ->  Seq Scan on order_item_attribute oia  (cost=0.00..270182.79 rows=3415656 width=23) (actual time=3.358..43398.974 rows=3428079 loops=1)
                           Filter: (attr_name = 'barcodeLogId'::text)
                     ->  Hash  (cost=408864.88..408864.88 rows=150100 width=19) (actual time=15253.572..15253.572 rows=177067 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 9289kB
                           ->  Bitmap Heap Scan on barcode_log bl  (cost=3902.28..408864.88 rows=150100 width=19) (actual time=294.124..14782.873 rows=177067 loops=1)
                                 Recheck Cond: ((scan_time >= '2014-06-25 13:00:00+01'::timestamp with time zone) AND (scan_time <= '2014-06-26 13:00:00+01'::timestamp with time zone))
                                 ->  Bitmap Index Scan on bl_st_idx  (cost=0.00..3864.75 rows=150100 width=0) (actual time=252.072..252.072 rows=177067 loops=1)
                                       Index Cond: ((scan_time >= '2014-06-25 13:00:00+01'::timestamp with time zone) AND (scan_time <= '2014-06-26 13:00:00+01'::timestamp with time zone))
               ->  Hash  (cost=7901.86..7901.86 rows=42069 width=6) (actual time=296.596..296.596 rows=43476 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 1634kB
                     ->  Seq Scan on order_header oh  (cost=0.00..7901.86 rows=42069 width=6) (actual time=0.059..200.901 rows=43476 loops=1)
                           Filter: (status_id = ANY ('{ORDER_SUBMITTED,ORDER_PARTIALLY_RECEIVED,ORDER_ACCEPTED,ORDER_COMPLETED,ORDER_REJECTED,ORDER_EXPIRED}'::text[]))
         ->  Index Scan using pk_order_item on order_item oi  (cost=0.00..7.18 rows=1 width=18) (actual time=0.156..0.180 rows=1 loops=2590)
               Index Cond: ((order_id = oh.order_id) AND (order_item_seq_id = oia.order_item_seq_id))
   ->  Index Scan using pk_product on product p  (cost=0.00..5.96 rows=1 width=17) (actual time=4.468..4.480 rows=1 loops=2590)
         Index Cond: (product_id = oi.product_id)
 Total runtime: 82789.727 ms
(23 rows)

UPDATE

I tweaked the query a little bit as suggested by adding a CTE. It did somewhat improve the performance, but it still takes over 10 minutes to query a 6 months range. A solution to this unresponsiveness would be that instead of displaying the report on the admin interface it'd just get sent as an email when it's done. I'd rather avoid this though.

The new query:

with barcodes as (
select order_id, order_item_seq_id, attr_value
from order_item_attribute
where attr_name = 'barcodeLogId'
)
SELECT bl.amazon_item_auk_sales_rank AS salesRank, p.mb_type_enum AS itemType, p.product_id AS productId,
bl.offering_price_value AS offeredPrice
FROM order_header oh
INNER JOIN order_item AS oi ON oh.order_id = oi.order_id and oh.status_id IN ('ORDER_SUBMITTED','ORDER_PARTIALLY_RECEIVED','ORDER_ACCEPTED','ORDER_COMPLETED','ORDER_REJECTED','ORDER_EXPIRED')
INNER JOIN product AS p ON oi.product_id = p.product_id
INNER JOIN barcodes AS barcodes ON oi.order_id = barcodes.order_id and oi.order_item_seq_id = barcodes.order_item_seq_id
INNER JOIN barcode_log AS bl ON bl.barcode_log_id = barcodes.attr_value
WHERE bl.scan_time >= '2014-06-25 12:00:00.000+00' AND bl.scan_time <= '2014-06-26 12:00:00.000+00';

The explain analyze for this:

 Nested Loop  (cost=693319.77..852270.33 rows=168 width=28) (actual time=41918.159..43989.210 rows=2664 loops=1)
    CTE barcodes
      ->  Seq Scan on order_item_attribute  (cost=0.00..270182.79 rows=3415656 width=23) (actual time=8.015..11779.850 rows=3428039 loops=1)
            Filter: (attr_name = 'barcodeLogId'::text)
    ->  Nested Loop  (cost=423136.98..581137.13 rows=168 width=17) (actual time=41918.033..42610.140 rows=2664 loops=1)
     ->  Hash Join  (cost=423136.98..538913.27 rows=6114 width=81) (actual time=41903.342..42242.221 rows=2664 loops=1)
           Hash Cond: (barcodes.order_id = oh.order_id)
           ->  Hash Join  (cost=414709.25..530220.58 rows=23294 width=75) (actual time=38105.475..38411.484 rows=6103 loops=1)
                 Hash Cond: (barcodes.attr_value = bl.barcode_log_id)
                 ->  CTE Scan on barcodes  (cost=0.00..68313.12 rows=3415656 width=96) (actual time=8.050..24518.736 rows=3428039 loops=1)
                 ->  Hash  (cost=412808.84..412808.84 rows=152033 width=19) (actual time=4288.756..4288.756 rows=177067 loops=1)
                       Buckets: 16384  Batches: 1  Memory Usage: 9289kB
                       ->  Bitmap Heap Scan on barcode_log bl  (cost=3954.09..412808.84 rows=152033 width=19) (actual time=244.305..3703.737 rows=177067 loops=1)
                             Recheck Cond: ((scan_time >= '2014-06-25 13:00:00+01'::timestamp with time zone) AND (scan_time <= '2014-06-26 13:00:00+01'::timestamp with time zone))
                             ->  Bitmap Index Scan on bl_st_idx  (cost=0.00..3916.08 rows=152033 width=0) (actual time=211.496..211.496 rows=177067 loops=1)
                                   Index Cond: ((scan_time >= '2014-06-25 13:00:00+01'::timestamp with time zone) AND (scan_time <= '2014-06-26 13:00:00+01'::timestamp with time zone))
           ->  Hash  (cost=7901.86..7901.86 rows=42069 width=6) (actual time=3796.579..3796.579 rows=43477 loops=1)
                 Buckets: 8192  Batches: 1  Memory Usage: 1635kB
                 ->  Seq Scan on order_header oh  (cost=0.00..7901.86 rows=42069 width=6) (actual time=36.298..3559.393 rows=43477 loops=1)
                       Filter: (status_id = ANY ('{ORDER_SUBMITTED,ORDER_PARTIALLY_RECEIVED,ORDER_ACCEPTED,ORDER_COMPLETED,ORDER_REJECTED,ORDER_EXPIRED}'::text[]))
     ->  Index Scan using pk_order_item on order_item oi  (cost=0.00..6.89 rows=1 width=18) (actual time=0.115..0.122 rows=1 loops=2664)
           Index Cond: ((order_id = oh.order_id) AND (order_item_seq_id = barcodes.order_item_seq_id))
    ->  Index Scan using pk_product on product p  (cost=0.00..5.64 rows=1 width=17) (actual time=0.490..0.501 rows=1 loops=2664)
     Index Cond: (product_id = oi.product_id)
  Total runtime: 44079.559 ms
 (25 rows)
share|improve this question
    
While 8 minutes is slow, there's no reason that just because a query for one day's data takes 8 minutes, that the same query for 2 months worth of data will take 60 times as long. –  Colin 't Hart Jun 26 at 13:09
    
Please update your question to include 1) a functional description of what the query is supposed to do; 2) table definitions including indexes; and 3) information about the distribution of the data: ie how many rows of how many total you expect to satisfy the different conditions that you have in your query. –  Colin 't Hart Jun 26 at 13:11
    
When I tried to set the date range to a year the application just threw a 500 error after ~6 minutes. –  user3711530 Jun 26 at 13:45
    
I guess I'd try to push the query into a CTE (WITH query), without the join on order_item_attribute, then join that table in the main query. Also, a multicolumn index on order_header (order_id, status_id) may help. –  dezso Jun 26 at 13:54
    
Two things - you have too many "text" fields - what's the matter with VARCHAR? You don't have an index on oia.attr_name, oh.status_id or bl.scan_time (do you really need millisecond precision?). Have you considered using Common Table Expressions and/or temporary tables? It would be good if you could provide DDL (i.e. that can be copied and pasted for those who have running PostgreSQL servers). Looking at your row counts, my "gut" is telling me that this query really shouldn't take this long (impossible to be certain of course). Are you running this on prod or on a DW machine? Read only? –  Vérace Jun 26 at 14:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.