How to speed up subquery

Question

Let's suppose we have:

select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
    exists
(select
    *
from
    lineitem,orders
where
    l_orderkey=o_orderkey
and
    l_commitdate<l_receiptdate)
GROUP by
    o_orderpriority
ORDER by
    o_orderpriority

from TPC-H benchmark. These are the tables:

CREATE TABLE LINEITEM
 ( 
L_ORDERKEY INTEGER REFERENCES ORDERS(O_ORDERKEY),
L_PARTKEY INTEGER REFERENCES PART(P_PARTKEY),
L_SUPPKEY INTEGER REFERENCES SUPPLIER(S_SUPPKEY),
L_LINENUMBER INTEGER,
L_QUANTITY INTEGER,
L_EXTENDEDPRICE NUMERIC (12,2),
L_DISCOUNT NUMERIC (12,2),
L_TAX NUMERIC (12,2),
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE ,
L_COMMITDATE DATE ,
L_RECEIPTDATE DATE ,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT CHAR (44),
L_PARTSUPPKEY CHAR (20) REFERENCES PARTSUPP(PS_PARTSUPPKEY)
)

and

CREATE TABLE ORDERS
 ( 
O_ORDERKEY INTEGER PRIMARY KEY ,
O_CUSTKEY INTEGER REFERENCES CUSTOMER(C_CUSTKEY),
O_ORDERSTATUS CHAR (1),
O_TOTALPRICE NUMERIC (12,2),
O_ORDERDATE DATE ,
O_ORDERPRIORITY CHAR (15),
O_CLERK CHAR (15),
O_SHIPPRIORITY INTEGER,
O_COMMENT CHAR (79)
)

Explain Analyze returns:

"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=14682.540..14683.017 rows=5 loops=1)"
"  Group Key: orders.o_orderpriority"
"  InitPlan 1 (returns $1)"
"    ->  Nested Loop  (cost=0.45..91327112.22 rows=19995359 width=0) (actual time=0.803..0.804 rows=1 loops=1)"
"          ->  Seq Scan on lineitem  (cost=0.00..2144850.95 rows=19995359 width=4) (actual time=0.240..0.240 rows=1 loops=1)"
"                Filter: (l_commitdate < l_receiptdate)"
"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.560..0.561 rows=1 loops=1)"
"                Cache Key: lineitem.l_orderkey"
"                Cache Mode: logical"
"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.555..0.555 rows=1 loops=1)"
"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"
"                      Heap Fetches: 1"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=14682.524..14682.999 rows=15 loops=1)"
"        Workers Planned: 2"
"        Params Evaluated: $1"
"        Workers Launched: 2"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=14676.302..14676.303 rows=5 loops=3)"
"              Sort Key: orders.o_orderpriority"
"              Sort Method: quicksort  Memory: 25kB"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=14676.265..14676.266 rows=5 loops=3)"
"                    Group Key: orders.o_orderpriority"
"                    Batches: 1  Memory Usage: 24kB"
"                    Worker 0:  Batches: 1  Memory Usage: 24kB"
"                    Worker 1:  Batches: 1  Memory Usage: 24kB"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=7272.919..14638.858 rows=191224 loops=3)"
"                          One-Time Filter: $1"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=7272.917..14628.994 rows=191224 loops=3)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"
"                                Rows Removed by Filter: 4808776"
"Planning Time: 6.722 ms"
"Execution Time: 14683.864 ms"

How can I speed up this query (and its subquery) using indexes, materialized views and/or other stuff?
If I change char(n) datatype to text, would I get better performances?

EDIT

CREATE INDEX o_orderdate_c_idx ON orderdate
    USING btree (o_orderdate ASC NULLS LAST)
ALTER TABLE orderdate CLUSTER ON o_orderdate_c_idx

"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=9026.877..9027.505 rows=5 loops=1)"
"  Group Key: orders.o_orderpriority"
"  InitPlan 1 (returns $1)"
"    ->  Nested Loop  (cost=0.45..91327077.11 rows=19995351 width=0) (actual time=0.025..0.025 rows=1 loops=1)"
"          ->  Seq Scan on lineitem  (cost=0.00..2144850.65 rows=19995351 width=4) (actual time=0.010..0.010 rows=1 loops=1)"
"                Filter: (l_commitdate < l_receiptdate)"
"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)"
"                Cache Key: lineitem.l_orderkey"
"                Cache Mode: logical"
"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)"
"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"
"                      Heap Fetches: 1"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=9026.871..9027.498 rows=15 loops=1)"
"        Workers Planned: 2"
"        Params Evaluated: $1"
"        Workers Launched: 2"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=9023.327..9023.328 rows=5 loops=3)"
"              Sort Key: orders.o_orderpriority"
"              Sort Method: quicksort  Memory: 25kB"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=9023.294..9023.295 rows=5 loops=3)"
"                    Group Key: orders.o_orderpriority"
"                    Batches: 1  Memory Usage: 24kB"
"                    Worker 0:  Batches: 1  Memory Usage: 24kB"
"                    Worker 1:  Batches: 1  Memory Usage: 24kB"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=4222.574..8991.249 rows=191224 loops=3)"
"                          One-Time Filter: $1"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=4222.572..8981.901 rows=191224 loops=3)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"
"                                Rows Removed by Filter: 4808776"
"Planning Time: 3.580 ms"
"Execution Time: 9027.807 ms"

EDIT 2

                      Table "public.orders"
     Column      |     Type      | Collation | Nullable | Default 
-----------------+---------------+-----------+----------+---------
 o_orderkey      | integer       |           | not null | 
 o_custkey       | integer       |           |          | 
 o_orderstatus   | character(1)  |           |          | 
 o_totalprice    | numeric(12,2) |           |          | 
 o_orderdate     | date          |           |          | 
 o_orderpriority | character(15) |           |          | 
 o_clerk         | character(15) |           |          | 
 o_shippriority  | integer       |           |          | 
 o_comment       | character(79) |           |          | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
    "o_orderdate_c_idx" btree (o_orderdate) CLUSTER
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

and EXPLAIN(ANALYZE, BUFFERS, SETTINGS) returns:

"Finalize GroupAggregate  (cost=734234.44..734235.71 rows=5 width=24) (actual time=13542.359..13542.849 rows=5 loops=1)"
"  Group Key: orders.o_orderpriority"
"  Buffers: shared hit=19 read=638299"
"  InitPlan 1 (returns $1)"
"    ->  Nested Loop  (cost=0.45..91327077.11 rows=19995351 width=0) (actual time=0.681..0.682 rows=1 loops=1)"
"          Buffers: shared hit=2 read=4"
"          ->  Seq Scan on lineitem  (cost=0.00..2144850.65 rows=19995351 width=4) (actual time=0.187..0.187 rows=1 loops=1)"
"                Filter: (l_commitdate < l_receiptdate)"
"                Buffers: shared read=1"
"          ->  Memoize  (cost=0.45..4.99 rows=1 width=4) (actual time=0.492..0.492 rows=1 loops=1)"
"                Cache Key: lineitem.l_orderkey"
"                Cache Mode: logical"
"                Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                Buffers: shared hit=2 read=3"
"                ->  Index Only Scan using orders_pkey on orders orders_1  (cost=0.43..4.98 rows=1 width=4) (actual time=0.487..0.487 rows=1 loops=1)"
"                      Index Cond: (o_orderkey = lineitem.l_orderkey)"
"                      Heap Fetches: 1"
"                      Buffers: shared hit=2 read=3"
"  ->  Gather Merge  (cost=734229.43..734230.60 rows=10 width=24) (actual time=13542.294..13542.783 rows=15 loops=1)"
"        Workers Planned: 2"
"        Params Evaluated: $1"
"        Workers Launched: 2"
"        Buffers: shared hit=19 read=638299"
"        ->  Sort  (cost=733229.41..733229.42 rows=5 width=24) (actual time=13537.578..13537.579 rows=5 loops=3)"
"              Sort Key: orders.o_orderpriority"
"              Sort Method: quicksort  Memory: 25kB"
"              Buffers: shared hit=17 read=638295"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Partial HashAggregate  (cost=733229.30..733229.35 rows=5 width=24) (actual time=13537.542..13537.543 rows=5 loops=3)"
"                    Group Key: orders.o_orderpriority"
"                    Batches: 1  Memory Usage: 24kB"
"                    Buffers: shared hit=3 read=638295"
"                    Worker 0:  Batches: 1  Memory Usage: 24kB"
"                    Worker 1:  Batches: 1  Memory Usage: 24kB"
"                    ->  Result  (cost=0.00..732048.00 rows=236260 width=16) (actual time=6079.412..13496.465 rows=191224 loops=3)"
"                          One-Time Filter: $1"
"                          Buffers: shared hit=3 read=638295"
"                          ->  Parallel Seq Scan on orders  (cost=0.00..732048.00 rows=236260 width=16) (actual time=6079.411..13485.183 rows=191224 loops=3)"
"                                Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01'::date))"
"                                Rows Removed by Filter: 4808776"
"                                Buffers: shared hit=3 read=638295"
"Planning:"
"  Buffers: shared hit=223 read=20"
"Planning Time: 5.517 ms"
"Execution Time: 13543.511 ms"

The subquery is already fast. It is also almost certainly wrong, as it is uncorrelated to the outer query. — jjanes, May 11 at 14:35
I agree with jjanes. The (ancient styled) join in the subquery looks wrong and most probably only a reference to the outer orders table should be used to make it a co-related subquery — a_horse_with_no_name, May 11 at 19:52
@jjanes can you explain please? These queries are not mine, but they belong to TPC-H benchmark; I am trying to optimize some of them — tail, May 11 at 20:18
Query 2.4.4.2 from tpc.org/tpc_documents_current_versions/pdf/tpc-h_v3.0.0.pdf does not show a join inside the and exists subquery like you have. — jjanes, May 12 at 2:49

Laurenz Albe · Accepted Answer · 2022-05-12 09:13:19Z

0

You need an index on orders (o_orderdate), which should avoid the expensive sequential scan.

If it doesn't, and the sequential scan is actually slower than the index scan, PostgreSQL is probably not configured properly: either random_page_cost is to high for your hardware, or effective_cache_size is too low. Disabling parallel query (max_parallel_workers_per_gather = 0) will also favor an index scan.

edited May 12 at 9:13

answered May 11 at 15:20

Laurenz Albe

34k3 gold badges29 silver badges54 bronze badges

If I use a cluster index on that attribute, the optimizer still uses a seqscan over orders table
– tail
May 11 at 15:52
I find that hard to believe.
– Laurenz Albe
May 11 at 16:24
Check my post I update it
– tail
May 11 at 16:27
Please show the output of \d orders.
– Laurenz Albe
May 11 at 16:28
Yes, of course. I have just edited my post. Please check it up
– tail
May 11 at 18:25

| Show 2 more comments

Stack Exchange Network

current community

your communities

more stack exchange communities

How to speed up subquery

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged postgresql query-performance optimization subquery or ask your own question.

Hot Network Questions

How to speed up subquery

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresql query-performance optimization subquery or ask your own question.

Related

Hot Network Questions