patched.log
application/octet-stream
Filename: patched.log
Type: application/octet-stream
Part: 0
select pg_backend_pid();
pg_backend_pid
----------------
25221
(1 row)
-- using 1590755192 as a seed to the RNG
explain analyze select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '86' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=424379.96..424379.98 rows=6 width=236) (actual time=24772.275..24772.276 rows=4 loops=1)
Sort Key: l_returnflag, l_linestatus
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=424379.72..424379.88 rows=6 width=236) (actual time=24772.152..24772.180 rows=4 loops=1)
Group Key: l_returnflag, l_linestatus
Peak Memory Usage: 37 kB
-> Seq Scan on lineitem (cost=0.00..187511.24 rows=5921712 width=25) (actual time=0.031..3352.788 rows=5923969 loops=1)
Filter: (l_shipdate <= '1998-09-06 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 77246
Planning Time: 1.893 ms
Execution Time: 24772.542 ms
(11 rows)
explain analyze select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 20
and p_type like '%TIN'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AFRICA'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AFRICA'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=221113.04..221113.04 rows=1 width=192) (actual time=3916.956..3916.979 rows=100 loops=1)
-> Sort (cost=221113.04..221113.04 rows=1 width=192) (actual time=3916.952..3916.964 rows=100 loops=1)
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
Sort Method: top-N heapsort Memory: 71kB
-> Hash Join (cost=26436.41..221113.03 rows=1 width=192) (actual time=767.156..3915.803 rows=472 loops=1)
Hash Cond: ((part.p_partkey = partsupp.ps_partkey) AND ((SubPlan 1) = partsupp.ps_supplycost))
-> Seq Scan on part (cost=0.00..7097.00 rows=897 width=30) (actual time=0.037..102.813 rows=784 loops=1)
Filter: (((p_type)::text ~~ '%TIN'::text) AND (p_size = 20))
Rows Removed by Filter: 199216
-> Hash (cost=20129.41..20129.41 rows=160000 width=172) (actual time=718.782..718.782 rows=156400 loops=1)
Buckets: 32768 Batches: 16 Memory Usage: 2205kB
-> Nested Loop (cost=2.96..20129.41 rows=160000 width=172) (actual time=0.156..480.897 rows=156400 loops=1)
-> Hash Join (cost=2.53..382.03 rows=2000 width=166) (actual time=0.116..9.438 rows=1955 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=144) (actual time=0.012..3.788 rows=10000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.084..0.084 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.07..2.47 rows=5 width=30) (actual time=0.064..0.078 rows=5 loops=1)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.011..0.014 rows=25 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.014..0.017 rows=1 loops=1)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using partsupp_ps_suppkey_idx on partsupp (cost=0.42..9.07 rows=80 width=14) (actual time=0.011..0.203 rows=80 loops=1955)
Index Cond: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Result Cache (cost=409.47..409.48 rows=1 width=32) (actual time=2.422..2.422 rows=1 loops=1256)
Cache Key: part.p_partkey
Cache Hits: 472 Cache Misses: 784 Cache Evictions: 0 Cache Overflows: 0
-> Aggregate (cost=409.46..409.47 rows=1 width=32) (actual time=3.874..3.874 rows=1 loops=784)
-> Nested Loop (cost=8.55..409.46 rows=1 width=6) (actual time=3.578..3.866 rows=1 loops=784)
Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
Rows Removed by Join Filter: 3
-> Seq Scan on region region_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=784)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Nested Loop (cost=8.55..408.35 rows=4 width=10) (actual time=3.554..3.857 rows=4 loops=784)
Join Filter: (supplier_1.s_nationkey = nation_1.n_nationkey)
Rows Removed by Join Filter: 96
-> Seq Scan on nation nation_1 (cost=0.00..1.25 rows=25 width=8) (actual time=0.002..0.006 rows=25 loops=784)
-> Materialize (cost=8.55..405.61 rows=4 width=10) (actual time=0.022..0.153 rows=4 loops=19600)
-> Hash Join (cost=8.55..405.59 rows=4 width=10) (actual time=0.532..3.801 rows=4 loops=784)
Hash Cond: (supplier_1.s_suppkey = partsupp_1.ps_suppkey)
-> Seq Scan on supplier supplier_1 (cost=0.00..322.00 rows=10000 width=8) (actual time=0.002..1.487 rows=10000 loops=784)
-> Hash (cost=8.50..8.50 rows=4 width=10) (actual time=0.028..0.028 rows=4 loops=784)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using partsupp_ps_partkey_idx on partsupp partsupp_1 (cost=0.42..8.50 rows=4 width=10) (actual time=0.019..0.022 rows=4 loops=784)
Index Cond: (ps_partkey = part.p_partkey)
Planning Time: 3.093 ms
Execution Time: 3917.404 ms
(52 rows)
explain analyze select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'HOUSEHOLD'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-02'
and l_shipdate > date '1995-03-02'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=305610.12..305610.15 rows=10 width=44) (actual time=2831.028..2831.031 rows=10 loops=1)
-> Sort (cost=305610.12..306413.41 rows=321317 width=44) (actual time=2831.027..2831.028 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> HashAggregate (cost=292767.40..298666.58 rows=321317 width=44) (actual time=2810.013..2824.176 rows=11447 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Planned Partitions: 32
Peak Memory Usage: 3729 kB
-> Nested Loop (cost=5847.99..280416.78 rows=321317 width=24) (actual time=78.511..2722.232 rows=30345 loops=1)
-> Hash Join (cost=5847.56..54857.30 rows=147989 width=12) (actual time=78.136..1030.698 rows=143526 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Seq Scan on orders (cost=0.00..44845.00 rows=715960 width=16) (actual time=0.018..549.129 rows=719155 loops=1)
Filter: (o_orderdate < '1995-03-02'::date)
Rows Removed by Filter: 780845
-> Hash (cost=5460.00..5460.00 rows=31005 width=4) (actual time=78.049..78.050 rows=30189 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1318kB
-> Seq Scan on customer (cost=0.00..5460.00 rows=31005 width=4) (actual time=0.012..66.063 rows=30189 loops=1)
Filter: (c_mktsegment = 'HOUSEHOLD'::bpchar)
Rows Removed by Filter: 119811
-> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..1.43 rows=9 width=16) (actual time=0.011..0.011 rows=0 loops=143526)
Index Cond: (l_orderkey = orders.o_orderkey)
Filter: (l_shipdate > '1995-03-02'::date)
Rows Removed by Filter: 4
Planning Time: 1.297 ms
Execution Time: 2831.631 ms
(25 rows)
explain analyze select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-03-01'
and o_orderdate < date '1993-03-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=128473.11..128578.52 rows=5 width=24) (actual time=1244.630..1263.494 rows=5 loops=1)
Group Key: orders.o_orderpriority
-> Sort (cost=128473.11..128508.23 rows=14048 width=16) (actual time=1240.878..1247.328 rows=52200 loops=1)
Sort Key: orders.o_orderpriority
Sort Method: quicksort Memory: 3813kB
-> Nested Loop Semi Join (cost=0.43..127505.34 rows=14048 width=16) (actual time=0.054..1200.444 rows=52200 loops=1)
-> Seq Scan on orders (cost=0.00..48595.00 rows=56751 width=20) (actual time=0.032..531.554 rows=56934 loops=1)
Filter: ((o_orderdate >= '1993-03-01'::date) AND (o_orderdate < '1993-06-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1443066
-> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..4.62 rows=5 width=4) (actual time=0.011..0.011 rows=1 loops=56934)
Index Cond: (l_orderkey = orders.o_orderkey)
Filter: (l_commitdate < l_receiptdate)
Rows Removed by Filter: 1
Planning Time: 0.643 ms
Execution Time: 1264.025 ms
(15 rows)
explain analyze select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= date '1995-01-01'
and o_orderdate < date '1995-01-01' + interval '1' year
group by
n_name
order by
revenue desc
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=193407.80..193407.87 rows=25 width=58) (actual time=2985.378..2985.379 rows=5 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: quicksort Memory: 25kB
-> GroupAggregate (cost=193315.08..193407.22 rows=25 width=58) (actual time=2976.896..2985.357 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=193315.08..193333.45 rows=7346 width=38) (actual time=2974.785..2975.686 rows=7378 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 769kB
-> Hash Join (cost=475.39..192843.37 rows=7346 width=38) (actual time=8.627..2962.671 rows=7378 loops=1)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Nested Loop (cost=3.39..190920.57 rows=183646 width=50) (actual time=0.171..2838.683 rows=183585 loops=1)
-> Nested Loop (cost=2.96..53259.63 rows=45906 width=38) (actual time=0.150..1796.202 rows=45731 loops=1)
-> Hash Join (cost=2.53..5950.03 rows=30000 width=38) (actual time=0.085..129.773 rows=30183 loops=1)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.017..54.685 rows=150000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.053..0.054 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.07..2.47 rows=5 width=30) (actual time=0.041..0.050 rows=5 loops=1)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.010..0.013 rows=25 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using orders_o_custkey_idx on orders (cost=0.43..1.55 rows=3 width=8) (actual time=0.024..0.053 rows=2 loops=30183)
Index Cond: (o_custkey = customer.c_custkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate < '1996-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 8
-> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..2.84 rows=16 width=20) (actual time=0.017..0.020 rows=4 loops=45731)
Index Cond: (l_orderkey = orders.o_orderkey)
-> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=7.828..7.828 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.009..3.807 rows=10000 loops=1)
Planning Time: 2.903 ms
Execution Time: 2985.531 ms
(36 rows)
explain analyze select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= date '1995-01-01'
and l_shipdate < date '1995-01-01' + interval '1' year
and l_discount between 0.02 - 0.01 and 0.02 + 0.01
and l_quantity < 24
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=248105.40..248105.42 rows=1 width=32) (actual time=2740.088..2740.088 rows=1 loops=1)
-> Seq Scan on lineitem (cost=0.00..247517.83 rows=117515 width=12) (actual time=0.093..2640.760 rows=114428 loops=1)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate < '1996-01-01 00:00:00'::timestamp without time zone) AND (l_discount >= 0.01) AND (l_discount <= 0.03) AND (l_quantity < '24'::numeric))
Rows Removed by Filter: 5886787
Planning Time: 0.193 ms
Execution Time: 2740.131 ms
(6 rows)
explain analyze select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'ALGERIA' and n2.n_name = 'JAPAN')
or (n1.n_name = 'JAPAN' and n2.n_name = 'ALGERIA')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=82923.89..83138.27 rows=6125 width=92) (actual time=3682.711..3689.796 rows=4 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=82923.89..82939.21 rows=6125 width=72) (actual time=3680.311..3680.959 rows=5923 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1025kB
-> Hash Join (cost=27675.57..82538.62 rows=6125 width=72) (actual time=710.699..3672.304 rows=5923 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
Join Filter: (((n1.n_name = 'ALGERIA'::bpchar) AND (n2.n_name = 'JAPAN'::bpchar)) OR ((n1.n_name = 'JAPAN'::bpchar) AND (n2.n_name = 'ALGERIA'::bpchar)))
Rows Removed by Join Filter: 5762
-> Nested Loop (cost=1.83..50637.81 rows=147185 width=46) (actual time=0.122..2700.822 rows=145780 loops=1)
-> Hash Join (cost=1.40..368.90 rows=800 width=30) (actual time=0.067..10.613 rows=797 loops=1)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.016..4.442 rows=10000 loops=1)
-> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.032..0.033 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on nation n1 (cost=0.00..1.38 rows=2 width=30) (actual time=0.014..0.026 rows=2 loops=1)
Filter: ((n_name = 'ALGERIA'::bpchar) OR (n_name = 'JAPAN'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using lineitem_l_suppkey_idx on lineitem (cost=0.43..61.00 rows=184 width=24) (actual time=0.048..3.307 rows=183 loops=797)
Index Cond: (l_suppkey = supplier.s_suppkey)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 418
-> Hash (cost=25352.74..25352.74 rows=120000 width=30) (actual time=708.885..708.886 rows=119027 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 2310kB
-> Nested Loop (cost=1.83..25352.74 rows=120000 width=30) (actual time=0.073..622.688 rows=119027 loops=1)
-> Hash Join (cost=1.40..5768.90 rows=12000 width=30) (actual time=0.048..90.861 rows=11873 loops=1)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.012..42.796 rows=150000 loops=1)
-> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.019..0.020 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on nation n2 (cost=0.00..1.38 rows=2 width=30) (actual time=0.006..0.016 rows=2 loops=1)
Filter: ((n_name = 'JAPAN'::bpchar) OR (n_name = 'ALGERIA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using orders_o_custkey_idx on orders (cost=0.43..1.46 rows=17 width=8) (actual time=0.007..0.041 rows=10 loops=11873)
Index Cond: (o_custkey = customer.c_custkey)
Planning Time: 2.698 ms
Execution Time: 3690.013 ms
(37 rows)
explain analyze select
o_year,
sum(case
when nation = 'JAPAN' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'ASIA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'STANDARD BRUSHED NICKEL'
) as all_nations
group by
o_year
order by
o_year
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=178115.45..178223.81 rows=2406 width=40) (actual time=932.145..933.935 rows=2 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=178115.45..178121.48 rows=2410 width=46) (actual time=930.406..930.636 rows=2358 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 281kB
-> Hash Join (cost=6774.45..177980.07 rows=2410 width=46) (actual time=115.322..927.921 rows=2358 loops=1)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=6772.89..177933.32 rows=2410 width=20) (actual time=115.251..923.393 rows=2358 loops=1)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=6325.89..177453.18 rows=2410 width=20) (actual time=105.728..911.264 rows=2358 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=0.86..171058.87 rows=12048 width=24) (actual time=0.295..795.821 rows=11668 loops=1)
-> Nested Loop (cost=0.43..151971.45 rows=39754 width=20) (actual time=0.138..312.746 rows=38455 loops=1)
-> Seq Scan on part (cost=0.00..6597.00 rows=1325 width=4) (actual time=0.091..69.127 rows=1285 loops=1)
Filter: ((p_type)::text = 'STANDARD BRUSHED NICKEL'::text)
Rows Removed by Filter: 198715
-> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..109.41 rows=31 width=24) (actual time=0.015..0.174 rows=30 loops=1285)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.47 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=38455)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Hash (cost=5950.03..5950.03 rows=30000 width=4) (actual time=105.361..105.362 rows=30183 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1318kB
-> Hash Join (cost=2.53..5950.03 rows=30000 width=4) (actual time=0.078..91.842 rows=30183 loops=1)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.012..43.199 rows=150000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=4) (actual time=0.056..0.056 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.07..2.47 rows=5 width=4) (actual time=0.044..0.053 rows=5 loops=1)
Hash Cond: (n1.n_regionkey = region.r_regionkey)
-> Seq Scan on nation n1 (cost=0.00..1.25 rows=25 width=8) (actual time=0.009..0.013 rows=25 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
-> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=9.488..9.488 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.012..5.788 rows=10000 loops=1)
-> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.048..0.048 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation n2 (cost=0.00..1.25 rows=25 width=30) (actual time=0.021..0.030 rows=25 loops=1)
Planning Time: 3.714 ms
Execution Time: 934.150 ms
(45 rows)
explain analyze select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%chartreuse%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=102141.53..102146.03 rows=120 width=66) (actual time=10756.440..11626.136 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=102141.53..102141.83 rows=120 width=57) (actual time=10752.992..10946.952 rows=325758 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: external merge Disk: 26752kB
-> Hash Join (cost=7172.68..102137.39 rows=120 width=57) (actual time=114.032..9940.619 rows=325758 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Nested Loop (cost=7171.12..102133.58 rows=120 width=31) (actual time=113.961..9371.538 rows=325758 loops=1)
-> Hash Join (cost=7170.69..102076.56 rows=120 width=31) (actual time=113.929..4410.268 rows=325758 loops=1)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=6723.69..101627.91 rows=120 width=35) (actual time=105.468..4095.256 rows=325758 loops=1)
-> Hash Join (cost=6723.26..35572.98 rows=39972 width=18) (actual time=105.381..584.591 rows=43444 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Seq Scan on partsupp (cost=0.00..25450.00 rows=800000 width=14) (actual time=0.017..222.045 rows=800000 loops=1)
-> Hash (cost=6597.00..6597.00 rows=10101 width=4) (actual time=105.222..105.222 rows=10861 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 510kB
-> Seq Scan on part (cost=0.00..6597.00 rows=10101 width=4) (actual time=0.032..98.280 rows=10861 loops=1)
Filter: ((p_name)::text ~~ '%chartreuse%'::text)
Rows Removed by Filter: 189139
-> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..1.64 rows=1 width=29) (actual time=0.015..0.076 rows=7 loops=43444)
Index Cond: (l_partkey = partsupp.ps_partkey)
Filter: (partsupp.ps_suppkey = l_suppkey)
Rows Removed by Filter: 22
-> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=8.419..8.420 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.012..4.541 rows=10000 loops=1)
-> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.47 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=325758)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.051..0.051 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.024..0.032 rows=25 loops=1)
Planning Time: 6.012 ms
Execution Time: 11633.695 ms
(33 rows)
explain analyze select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1994-11-01'
and o_orderdate < date '1994-11-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=267216.65..267216.70 rows=20 width=202) (actual time=4372.507..4372.512 rows=20 loops=1)
-> Sort (cost=267216.65..267360.05 rows=57358 width=202) (actual time=4372.504..4372.507 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=263396.06..265690.38 rows=57358 width=202) (actual time=4006.955..4340.154 rows=38271 loops=1)
Group Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment
-> Sort (cost=263396.06..263539.45 rows=57358 width=182) (actual time=4006.919..4049.702 rows=115282 loops=1)
Sort Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment
Sort Method: external merge Disk: 26048kB
-> Hash Join (cost=243753.31..253763.06 rows=57358 width=182) (actual time=3361.020..3700.087 rows=115282 loops=1)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Hash Join (cost=243751.75..252972.83 rows=57358 width=160) (actual time=3360.962..3634.639 rows=115282 loops=1)
Hash Cond: (customer.c_custkey = orders.o_custkey)
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=148) (actual time=0.010..44.478 rows=150000 loops=1)
-> Hash (cost=243034.77..243034.77 rows=57358 width=16) (actual time=3360.768..3360.769 rows=115282 loops=1)
Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3810kB
-> Hash Join (cost=49310.09..243034.77 rows=57358 width=16) (actual time=509.380..3277.979 rows=115282 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Seq Scan on lineitem (cost=0.00..187511.24 rows=1503965 width=16) (actual time=0.018..2297.092 rows=1478870 loops=1)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 4522345
-> Hash (cost=48595.00..48595.00 rows=57207 width=8) (actual time=509.045..509.046 rows=57683 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2766kB
-> Seq Scan on orders (cost=0.00..48595.00 rows=57207 width=8) (actual time=0.042..478.221 rows=57683 loops=1)
Filter: ((o_orderdate >= '1994-11-01'::date) AND (o_orderdate < '1995-02-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1442317
-> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.046..0.046 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.020..0.029 rows=25 loops=1)
Planning Time: 1.604 ms
Execution Time: 4379.934 ms
(31 rows)
explain analyze select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'MOZAMBIQUE'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0001000000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'MOZAMBIQUE'
)
order by
value desc
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=11483.54..11510.20 rows=10667 width=36) (actual time=400.815..400.907 rows=838 loops=1)
Sort Key: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric))) DESC
Sort Method: quicksort Memory: 64kB
InitPlan 1 (returns $1)
-> Aggregate (cost=4554.30..4554.32 rows=1 width=32) (actual time=100.052..100.052 rows=1 loops=1)
-> Nested Loop (cost=1.75..4314.30 rows=32000 width=10) (actual time=0.081..71.156 rows=32480 loops=1)
-> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.062..5.190 rows=406 loops=1)
Hash Cond: (supplier_1.s_nationkey = nation_1.n_nationkey)
-> Seq Scan on supplier supplier_1 (cost=0.00..322.00 rows=10000 width=8) (actual time=0.013..1.963 rows=10000 loops=1)
-> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on nation nation_1 (cost=0.00..1.31 rows=1 width=4) (actual time=0.018..0.020 rows=1 loops=1)
Filter: (n_name = 'MOZAMBIQUE'::bpchar)
Rows Removed by Filter: 24
-> Index Scan using partsupp_ps_suppkey_idx on partsupp partsupp_1 (cost=0.42..9.07 rows=80 width=14) (actual time=0.009..0.137 rows=80 loops=406)
Index Cond: (ps_suppkey = supplier_1.s_suppkey)
-> HashAggregate (cost=5499.30..6215.55 rows=10667 width=36) (actual time=342.414..400.174 rows=838 loops=1)
Group Key: partsupp.ps_partkey
Filter: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric)) > $1)
Planned Partitions: 4
Peak Memory Usage: 4017 kB
Disk Usage: 768 kB
HashAgg Batches: 4
Rows Removed by Filter: 29832
-> Nested Loop (cost=1.75..4314.30 rows=32000 width=14) (actual time=0.093..164.846 rows=32480 loops=1)
-> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.063..6.626 rows=406 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.015..3.210 rows=10000 loops=1)
-> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.019..0.023 rows=1 loops=1)
Filter: (n_name = 'MOZAMBIQUE'::bpchar)
Rows Removed by Filter: 24
-> Index Scan using partsupp_ps_suppkey_idx on partsupp (cost=0.42..9.07 rows=80 width=18) (actual time=0.016..0.349 rows=80 loops=406)
Index Cond: (ps_suppkey = supplier.s_suppkey)
Planning Time: 1.134 ms
Execution Time: 401.511 ms
(37 rows)
explain analyze select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('MAIL', 'RAIL')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1995-01-01'
and l_receiptdate < date '1995-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=316003.57..316576.24 rows=7 width=27) (actual time=4717.020..4730.563 rows=2 loops=1)
Group Key: lineitem.l_shipmode
-> Sort (cost=316003.57..316075.15 rows=28630 width=27) (actual time=4703.711..4706.661 rows=30952 loops=1)
Sort Key: lineitem.l_shipmode
Sort Method: quicksort Memory: 3187kB
-> Merge Join (cost=244645.73..313884.20 rows=28630 width=27) (actual time=3709.791..4680.796 rows=30952 loops=1)
Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..65062.43 rows=1500000 width=20) (actual time=0.027..665.729 rows=1499856 loops=1)
-> Sort (cost=244642.33..244713.90 rows=28630 width=15) (actual time=3709.677..3715.227 rows=30952 loops=1)
Sort Key: lineitem.l_orderkey
Sort Method: quicksort Memory: 2219kB
-> Bitmap Heap Scan on lineitem (cost=23267.92..242522.95 rows=28630 width=15) (actual time=424.482..3676.636 rows=30952 loops=1)
Recheck Cond: (l_shipmode = ANY ('{MAIL,RAIL}'::bpchar[]))
Rows Removed by Index Recheck: 2499312
Filter: ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1995-01-01'::date) AND (l_receiptdate < '1996-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 1682933
Heap Blocks: exact=46349 lossy=66154
-> Bitmap Index Scan on lineitem_l_shipmode_l_partkey_idx (cost=0.00..23260.76 rows=1687985 width=0) (actual time=405.823..405.823 rows=1713885 loops=1)
Index Cond: (l_shipmode = ANY ('{MAIL,RAIL}'::bpchar[]))
Planning Time: 0.873 ms
Execution Time: 4732.371 ms
(21 rows)
explain analyze select
c_count,
count(*) as custdist
from
(
select
c_custkey,
count(o_orderkey)
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%pending%accounts%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=125870.78..125871.28 rows=200 width=16) (actual time=5178.186..5178.190 rows=42 loops=1)
Sort Key: (count(*)) DESC, (count(orders.o_orderkey)) DESC
Sort Method: quicksort Memory: 26kB
-> HashAggregate (cost=125861.13..125863.13 rows=200 width=16) (actual time=5178.120..5178.142 rows=42 loops=1)
Group Key: count(orders.o_orderkey)
Peak Memory Usage: 37 kB
-> HashAggregate (cost=116252.34..123611.13 rows=150000 width=12) (actual time=4253.005..5105.228 rows=150000 loops=1)
Group Key: customer.c_custkey
Planned Partitions: 4
Peak Memory Usage: 4249 kB
Disk Usage: 31488 kB
HashAgg Batches: 84
-> Hash Right Join (cost=7546.00..85317.94 rows=1499850 width=8) (actual time=108.981..2928.159 rows=1533620 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Seq Scan on orders (cost=0.00..44845.00 rows=1499850 width=8) (actual time=0.023..1144.170 rows=1483615 loops=1)
Filter: ((o_comment)::text !~~ '%pending%accounts%'::text)
Rows Removed by Filter: 16385
-> Hash (cost=5085.00..5085.00 rows=150000 width=4) (actual time=108.680..108.681 rows=150000 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 3662kB
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=4) (actual time=0.016..51.533 rows=150000 loops=1)
Planning Time: 0.620 ms
Execution Time: 5187.022 ms
(22 rows)
explain analyze select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1995-08-01'
and l_shipdate < date '1995-08-01' + interval '1' month
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=217111.11..217111.12 rows=1 width=32) (actual time=2816.912..2816.912 rows=1 loops=1)
-> Hash Join (cost=9965.00..215718.26 rows=79591 width=33) (actual time=160.786..2692.218 rows=77505 loops=1)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Seq Scan on lineitem (cost=0.00..202512.89 rows=79591 width=16) (actual time=0.030..2339.165 rows=77505 loops=1)
Filter: ((l_shipdate >= '1995-08-01'::date) AND (l_shipdate < '1995-09-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 5923710
-> Hash (cost=6097.00..6097.00 rows=200000 width=25) (actual time=160.581..160.582 rows=200000 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 3406kB
-> Seq Scan on part (cost=0.00..6097.00 rows=200000 width=25) (actual time=0.011..71.950 rows=200000 loops=1)
Planning Time: 0.572 ms
Execution Time: 2816.999 ms
(11 rows)
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1996-08-01'
and l_shipdate < date '1996-08-01' + interval '3' month
group by
l_suppkey;
CREATE VIEW
explain analyze select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=412786.19..412836.94 rows=50 width=103) (actual time=5668.358..5668.360 rows=1 loops=1)
Merge Cond: (supplier.s_suppkey = revenue0.supplier_no)
InitPlan 1 (returns $0)
-> Aggregate (cost=205070.74..205070.75 rows=1 width=32) (actual time=2894.522..2894.522 rows=1 loops=1)
-> HashAggregate (cost=204821.24..204945.99 rows=9980 width=36) (actual time=2881.382..2892.621 rows=10000 loops=1)
Group Key: lineitem_1.l_suppkey
Peak Memory Usage: 3217 kB
-> Seq Scan on lineitem lineitem_1 (cost=0.00..202512.89 rows=230835 width=16) (actual time=0.040..2388.038 rows=230590 loops=1)
Filter: ((l_shipdate >= '1996-08-01'::date) AND (l_shipdate < '1996-11-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 5770625
-> Sort (cost=986.39..1011.39 rows=10000 width=71) (actual time=9.938..10.508 rows=5473 loops=1)
Sort Key: supplier.s_suppkey
Sort Method: quicksort Memory: 1791kB
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=71) (actual time=0.019..4.684 rows=10000 loops=1)
-> Sort (cost=206729.06..206729.18 rows=50 width=36) (actual time=5656.912..5656.913 rows=1 loops=1)
Sort Key: revenue0.supplier_no
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on revenue0 (cost=206552.50..206727.65 rows=50 width=36) (actual time=5654.186..5656.893 rows=1 loops=1)
-> HashAggregate (cost=206552.50..206727.15 rows=50 width=36) (actual time=5654.181..5656.887 rows=1 loops=1)
Group Key: lineitem.l_suppkey
Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $0)
Peak Memory Usage: 3217 kB
Rows Removed by Filter: 9999
-> Seq Scan on lineitem (cost=0.00..202512.89 rows=230835 width=16) (actual time=0.040..2276.662 rows=230590 loops=1)
Filter: ((l_shipdate >= '1996-08-01'::date) AND (l_shipdate < '1996-11-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 5770625
Planning Time: 0.587 ms
Execution Time: 5668.661 ms
(28 rows)
drop view revenue0
;
DROP VIEW
explain analyze select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#24'
and p_type not like 'STANDARD POLISHED%'
and p_size in (19, 24, 46, 31, 42, 22, 40, 27)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=45845.10..45884.66 rows=15825 width=44) (actual time=1660.715..1662.576 rows=18243 loops=1)
Sort Key: (count(DISTINCT partsupp.ps_suppkey)) DESC, part.p_brand, part.p_type, part.p_size
Sort Method: quicksort Memory: 2209kB
-> GroupAggregate (cost=43865.80..44741.31 rows=15825 width=44) (actual time=1361.067..1582.645 rows=18243 loops=1)
Group Key: part.p_brand, part.p_type, part.p_size
-> Sort (cost=43865.80..44009.25 rows=57381 width=40) (actual time=1361.013..1453.084 rows=117673 loops=1)
Sort Key: part.p_brand, part.p_type, part.p_size
Sort Method: external merge Disk: 6976kB
-> Hash Join (cost=9806.52..39330.33 rows=57381 width=40) (actual time=149.949..859.963 rows=117673 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Seq Scan on partsupp (cost=347.00..27797.00 rows=400000 width=8) (actual time=6.060..456.090 rows=799680 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 320
SubPlan 1
-> Seq Scan on supplier (cost=0.00..347.00 rows=1 width=4) (actual time=0.224..6.015 rows=4 loops=1)
Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text)
Rows Removed by Filter: 9996
-> Hash (cost=9097.00..9097.00 rows=29001 width=40) (actual time=143.744..143.746 rows=29430 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 2385kB
-> Seq Scan on part (cost=0.00..9097.00 rows=29001 width=40) (actual time=0.038..125.993 rows=29430 loops=1)
Filter: ((p_brand <> 'Brand#24'::bpchar) AND ((p_type)::text !~~ 'STANDARD POLISHED%'::text) AND (p_size = ANY ('{19,24,46,31,42,22,40,27}'::integer[])))
Rows Removed by Filter: 170570
Planning Time: 0.812 ms
Execution Time: 1665.690 ms
(24 rows)
explain analyze select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#41'
and p_container = 'LG DRUM'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
)
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=766387.05..766387.06 rows=1 width=32) (actual time=138.852..138.852 rows=1 loops=1)
-> Nested Loop (cost=0.43..766382.39 rows=1860 width=8) (actual time=0.882..138.585 rows=518 loops=1)
-> Seq Scan on part (cost=0.00..7097.00 rows=186 width=4) (actual time=0.559..71.978 rows=201 loops=1)
Filter: ((p_brand = 'Brand#41'::bpchar) AND (p_container = 'LG DRUM'::bpchar))
Rows Removed by Filter: 199799
-> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..4082.08 rows=10 width=17) (actual time=0.274..0.328 rows=3 loops=201)
Index Cond: (l_partkey = part.p_partkey)
Filter: (l_quantity < (SubPlan 1))
Rows Removed by Filter: 27
SubPlan 1
-> Result Cache (cost=127.61..127.62 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=5899)
Cache Key: part.p_partkey
Cache Hits: 5698 Cache Misses: 201 Cache Evictions: 0 Cache Overflows: 0
-> Aggregate (cost=127.60..127.61 rows=1 width=32) (actual time=0.225..0.225 rows=1 loops=201)
-> Bitmap Heap Scan on lineitem lineitem_1 (cost=4.67..127.52 rows=31 width=5) (actual time=0.024..0.200 rows=29 loops=201)
Recheck Cond: (l_partkey = part.p_partkey)
Heap Blocks: exact=5898
-> Bitmap Index Scan on lineitem_l_partkey_idx (cost=0.00..4.67 rows=31 width=0) (actual time=0.010..0.010 rows=29 loops=201)
Index Cond: (l_partkey = part.p_partkey)
Planning Time: 0.636 ms
Execution Time: 138.977 ms
(21 rows)
explain analyze select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey having
sum(l_quantity) > 312
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=378882.01..378882.26 rows=100 width=71) (actual time=8534.046..8534.048 rows=10 loops=1)
-> Sort (cost=378882.01..380119.80 rows=495118 width=71) (actual time=8534.043..8534.045 rows=10 loops=1)
Sort Key: orders.o_totalprice DESC, orders.o_orderdate
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=349418.36..359958.95 rows=495118 width=71) (actual time=8533.921..8534.012 rows=10 loops=1)
Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey
Planned Partitions: 64
Peak Memory Usage: 409 kB
-> Nested Loop (cost=7840.31..324585.09 rows=495118 width=44) (actual time=1949.776..8533.613 rows=70 loops=1)
Join Filter: (orders.o_orderkey = lineitem.l_orderkey)
-> Hash Join (cost=7839.87..292610.55 rows=123766 width=43) (actual time=1949.709..8532.986 rows=10 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=0.87..280738.77 rows=123766 width=24) (actual time=1784.945..8345.592 rows=10 loops=1)
-> GroupAggregate (cost=0.43..276121.27 rows=123766 width=4) (actual time=1784.881..8345.071 rows=10 loops=1)
Group Key: lineitem_1.l_orderkey
Filter: (sum(lineitem_1.l_quantity) > '312'::numeric)
Rows Removed by Filter: 1499990
-> Index Scan using lineitem_l_orderkey_idx on lineitem lineitem_1 (cost=0.43..240548.52 rows=6000659 width=9) (actual time=0.050..3161.243 rows=6001215 loops=1)
-> Result Cache (cost=0.44..1.43 rows=1 width=20) (actual time=0.044..0.045 rows=1 loops=10)
Cache Key: lineitem_1.l_orderkey
Cache Hits: 0 Cache Misses: 10 Cache Evictions: 0 Cache Overflows: 0
-> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..1.42 rows=1 width=20) (actual time=0.033..0.034 rows=1 loops=10)
Index Cond: (o_orderkey = lineitem_1.l_orderkey)
-> Hash (cost=5085.00..5085.00 rows=150000 width=23) (actual time=164.622..164.622 rows=150000 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 2569kB
-> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=23) (actual time=0.020..76.100 rows=150000 loops=1)
-> Result Cache (cost=0.44..3.61 rows=16 width=9) (actual time=0.041..0.054 rows=7 loops=10)
Cache Key: lineitem_1.l_orderkey
Cache Hits: 0 Cache Misses: 10 Cache Evictions: 0 Cache Overflows: 0
-> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..3.60 rows=16 width=9) (actual time=0.033..0.041 rows=7 loops=10)
Index Cond: (l_orderkey = lineitem_1.l_orderkey)
Planning Time: 1.720 ms
Execution Time: 8534.360 ms
(33 rows)
explain analyze select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 8 and l_quantity <= 8 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#53'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 14 and l_quantity <= 14 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#52'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 24 and l_quantity <= 24 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=24643.39..24643.40 rows=1 width=32) (actual time=155.368..155.369 rows=1 loops=1)
-> Nested Loop (cost=0.43..24642.51 rows=116 width=12) (actual time=1.557..155.027 rows=141 loops=1)
-> Seq Scan on part (cost=0.00..12597.00 rows=490 width=30) (actual time=0.552..126.899 rows=530 loops=1)
Filter: ((p_size >= 1) AND (((p_brand = 'Brand#23'::bpchar) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (p_size <= 5)) OR ((p_brand = 'Brand#53'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10)) OR ((p_brand = 'Brand#52'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (p_size <= 15))))
Rows Removed by Filter: 199470
-> Index Scan using lineitem_l_shipmode_l_partkey_idx on lineitem (cost=0.43..24.57 rows=1 width=21) (actual time=0.044..0.049 rows=0 loops=530)
Index Cond: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (l_partkey = part.p_partkey))
Filter: ((l_shipinstruct = 'DELIVER IN PERSON'::bpchar) AND (((l_quantity >= '8'::numeric) AND (l_quantity <= '18'::numeric)) OR ((l_quantity >= '14'::numeric) AND (l_quantity <= '24'::numeric)) OR ((l_quantity >= '24'::numeric) AND (l_quantity <= '34'::numeric))) AND (((part.p_brand = 'Brand#23'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (l_quantity >= '8'::numeric) AND (l_quantity <= '18'::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#53'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (l_quantity >= '14'::numeric) AND (l_quantity <= '24'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#52'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (l_quantity >= '24'::numeric) AND (l_quantity <= '34'::numeric) AND (part.p_size <= 15))))
Rows Removed by Filter: 4
Planning Time: 1.192 ms
Execution Time: 155.479 ms
(11 rows)
explain analyze select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like 'lavender%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1997-01-01'
and l_shipdate < date '1997-01-01' + interval '1' year
)
)
and s_nationkey = n_nationkey
and n_name = 'KENYA'
order by
s_name
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=164779.59..164779.86 rows=107 width=51) (actual time=2293.056..2293.068 rows=152 loops=1)
Sort Key: supplier.s_name
Sort Method: quicksort Memory: 43kB
-> Hash Semi Join (cost=164409.98..164775.99 rows=107 width=51) (actual time=2287.597..2292.564 rows=152 loops=1)
Hash Cond: (supplier.s_suppkey = partsupp.ps_suppkey)
-> Hash Join (cost=1.32..364.82 rows=400 width=55) (actual time=0.058..4.938 rows=376 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=59) (actual time=0.013..2.405 rows=10000 loops=1)
-> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.032..0.033 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.023..0.027 rows=1 loops=1)
Filter: (n_name = 'KENYA'::bpchar)
Rows Removed by Filter: 24
-> Hash (cost=164375.34..164375.34 rows=2665 width=4) (actual time=2287.449..2287.449 rows=5887 loops=1)
Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 271kB
-> Nested Loop (cost=6602.48..164375.34 rows=2665 width=4) (actual time=64.623..2281.596 rows=5887 loops=1)
-> HashAggregate (cost=6602.05..6622.25 rows=2020 width=4) (actual time=63.973..65.914 rows=2155 loops=1)
Group Key: part.p_partkey
Peak Memory Usage: 241 kB
-> Seq Scan on part (cost=0.00..6597.00 rows=2020 width=4) (actual time=0.166..61.574 rows=2155 loops=1)
Filter: ((p_name)::text ~~ 'lavender%'::text)
Rows Removed by Filter: 197845
-> Index Scan using partsupp_ps_partkey_idx on partsupp (cost=0.42..78.09 rows=1 width=8) (actual time=0.382..1.025 rows=3 loops=2155)
Index Cond: (ps_partkey = part.p_partkey)
Filter: ((ps_availqty)::numeric > (SubPlan 1))
Rows Removed by Filter: 1
SubPlan 1
-> Result Cache (cost=17.89..17.90 rows=1 width=32) (actual time=0.249..0.249 rows=1 loops=8620)
Cache Key: partsupp.ps_partkey, partsupp.ps_suppkey
Cache Hits: 0 Cache Misses: 8620 Cache Evictions: 0 Cache Overflows: 0
-> Aggregate (cost=17.88..17.89 rows=1 width=32) (actual time=0.246..0.246 rows=1 loops=8620)
-> Bitmap Heap Scan on lineitem (cost=13.86..17.88 rows=1 width=5) (actual time=0.221..0.240 rows=1 loops=8620)
Recheck Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Filter: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 6
Heap Blocks: exact=65248
-> BitmapAnd (cost=13.86..13.86 rows=1 width=0) (actual time=0.189..0.189 rows=0 loops=8620)
-> Bitmap Index Scan on lineitem_l_partkey_idx (cost=0.00..4.67 rows=31 width=0) (actual time=0.012..0.012 rows=30 loops=8620)
Index Cond: (l_partkey = partsupp.ps_partkey)
-> Bitmap Index Scan on lineitem_l_suppkey_idx (cost=0.00..8.94 rows=601 width=0) (actual time=0.170..0.170 rows=600 loops=8620)
Index Cond: (l_suppkey = partsupp.ps_suppkey)
Planning Time: 1.268 ms
Execution Time: 2293.304 ms
(43 rows)
explain analyze select
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'RUSSIA'
group by
s_name
order by
numwait desc,
s_name
limit 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=76037.74..76037.75 rows=1 width=34) (actual time=3352.850..3352.871 rows=100 loops=1)
-> Sort (cost=76037.74..76037.75 rows=1 width=34) (actual time=3352.848..3352.858 rows=100 loops=1)
Sort Key: (count(*)) DESC, supplier.s_name
Sort Method: top-N heapsort Memory: 37kB
-> GroupAggregate (cost=76037.71..76037.73 rows=1 width=34) (actual time=3350.571..3352.260 rows=401 loops=1)
Group Key: supplier.s_name
-> Sort (cost=76037.71..76037.72 rows=1 width=26) (actual time=3350.552..3350.887 rows=3970 loops=1)
Sort Key: supplier.s_name
Sort Method: quicksort Memory: 407kB
-> Nested Loop (cost=1.72..76037.70 rows=1 width=26) (actual time=0.694..3343.831 rows=3970 loops=1)
-> Nested Loop Semi Join (cost=1.30..76037.18 rows=1 width=34) (actual time=0.169..3197.456 rows=8097 loops=1)
-> Nested Loop Anti Join (cost=0.86..76036.17 rows=1 width=34) (actual time=0.160..3110.915 rows=13583 loops=1)
-> Nested Loop (cost=0.43..25045.77 rows=80009 width=34) (actual time=0.081..1458.019 rows=152495 loops=1)
-> Nested Loop (cost=0.00..448.31 rows=400 width=30) (actual time=0.040..7.027 rows=401 loops=1)
Join Filter: (supplier.s_nationkey = nation.n_nationkey)
Rows Removed by Join Filter: 9599
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1)
Filter: (n_name = 'RUSSIA'::bpchar)
Rows Removed by Filter: 24
-> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=34) (actual time=0.004..3.847 rows=10000 loops=1)
-> Index Scan using lineitem_l_suppkey_idx on lineitem l1 (cost=0.43..59.49 rows=200 width=8) (actual time=0.035..3.497 rows=380 loops=401)
Index Cond: (l_suppkey = supplier.s_suppkey)
Filter: (l_receiptdate > l_commitdate)
Rows Removed by Filter: 221
-> Index Scan using lineitem_l_orderkey_idx on lineitem l3 (cost=0.43..1.04 rows=5 width=8) (actual time=0.010..0.010 rows=1 loops=152495)
Index Cond: (l_orderkey = l1.l_orderkey)
Filter: ((l_receiptdate > l_commitdate) AND (l_suppkey <> l1.l_suppkey))
Rows Removed by Filter: 1
-> Index Scan using lineitem_l_orderkey_idx on lineitem l2 (cost=0.43..1.00 rows=16 width=8) (actual time=0.005..0.005 rows=1 loops=13583)
Index Cond: (l_orderkey = l1.l_orderkey)
Filter: (l_suppkey <> l1.l_suppkey)
Rows Removed by Filter: 1
-> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.51 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=8097)
Index Cond: (o_orderkey = l1.l_orderkey)
Filter: (o_orderstatus = 'F'::bpchar)
Rows Removed by Filter: 1
Planning Time: 3.000 ms
Execution Time: 3353.013 ms
(38 rows)
explain analyze select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from
(
select
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
from
customer
where
substring(c_phone from 1 for 2) in
('24', '30', '20', '29', '23', '14', '11')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('24', '30', '20', '29', '23', '14', '11')
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=16299.85..16320.28 rows=743 width=72) (actual time=499.525..502.688 rows=7 loops=1)
Group Key: ("substring"((customer.c_phone)::text, 1, 2))
InitPlan 1 (returns $0)
-> Aggregate (cost=7534.45..7534.46 rows=1 width=32) (actual time=224.285..224.286 rows=1 loops=1)
-> Seq Scan on customer customer_1 (cost=0.00..7522.50 rows=4779 width=6) (actual time=0.015..211.952 rows=38163 loops=1)
Filter: ((c_acctbal > 0.00) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{24,30,20,29,23,14,11}'::text[])))
Rows Removed by Filter: 111837
-> Sort (cost=8765.39..8767.25 rows=743 width=38) (actual time=498.995..499.590 rows=6431 loops=1)
Sort Key: ("substring"((customer.c_phone)::text, 1, 2))
Sort Method: quicksort Memory: 494kB
-> Nested Loop Anti Join (cost=0.43..8729.96 rows=743 width=38) (actual time=224.353..494.374 rows=6431 loops=1)
-> Seq Scan on customer (cost=0.00..7522.50 rows=1750 width=26) (actual time=224.317..387.733 rows=19083 loops=1)
Filter: ((c_acctbal > $0) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{24,30,20,29,23,14,11}'::text[])))
Rows Removed by Filter: 130917
-> Index Only Scan using orders_o_custkey_idx on orders (cost=0.43..3.28 rows=17 width=4) (actual time=0.004..0.004 rows=1 loops=19083)
Index Cond: (o_custkey = customer.c_custkey)
Heap Fetches: 0
Planning Time: 0.544 ms
Execution Time: 502.787 ms
(19 rows)