On 03/29/2017 09:00 PM, Robert Haas wrote:
On Mon, Mar 6, 2017 at 1:22 AM, Rafia Sabih <rafia.sa...@enterprisedb.com> wrote:This is to bring to notice a peculiar instance I found recently while running TPC-H benchmark queries. Q20 of the benchmark took 19 hours to complete ...That's bad.It is clear that selectivity estimations are really bad in this case particularly at node, -> Merge Join (cost=52959586.72..60024468.82 rows=85 width=16) (actual time=1525322.753..2419045.641 rows=1696742 loops=1) Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) Join Filter: ((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity)))) Rows Removed by Join Filter: 3771So, the selectivity estimation here is bad both before and after Tom's commit, but it's significantly worse after (actual value 1696742, old estimate 3771, new estimate 85).Still this puzzled me as during earlier runs of this benchmark I never encountered such prolonged running times. On further investigation I found that on reverting the commit 7fa93eec4e0c9c3e801e3c51aa4bae3a38aaa218 Author: Tom Lane <t...@sss.pgh.pa.us> Date: Sat Dec 17 15:28:54 2016 -0500 Fix FK-based join selectivity estimation for semi/antijoins.I don't think the problem originates at the Merge Join, though, because the commit says that at is fixing semi and anti-join estimates - this is a plain inner join, so in theory it shouldn't change. However, it's a bit hard for me to piece through these plans, the formatting kind of got messed up - things are wrapped. Could you possibly attach the plans as attachments?
I've been looking into this today, and it seems to me the simplest query triggering this issue (essentially a part of q20) is this:
select
ps_suppkey
from
partsupp,
(
select
l_partkey agg_partkey,
l_suppkey agg_suppkey
from
lineitem
group by
l_partkey,
l_suppkey
) agg_lineitem
where
agg_partkey = ps_partkey
and agg_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
which does actually include a semijoin. What seems to trigger the issue
is the join to the aggregated lineitem table - when replacing it with a
plain table, everything seems to be estimated perfectly fine.
Attached is a simple SQL script, that runs three variants of the query: (1) with the join to the aggregated lineitem table (2) with a join to a plain lineitem table(3) with a join to a plain lineitem table and to 'part' (without the semijoin)
First the queries are executed on tables without any foreign keys (between those three), then with a FK between lineitem and partsupp, and finally with additional FK between partsupp and part.
Initially the estimates are bad, but once the first foreign key is added, the estimates get very accurate - except for the case (1).
I've only ran the queries on 10GB data set, but that should be enough. The plans are from current master - I'll rerun the script on an older release later today.
regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
q20.sql
Description: application/sql
set max_parallel_workers_per_gather = 0;
SET
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
l_orderkey | integer | | not null |
l_partkey | integer | | not null |
l_suppkey | integer | | not null |
l_linenumber | integer | | not null |
l_quantity | numeric | | |
l_extendedprice | numeric | | |
l_discount | numeric | | |
l_tax | numeric | | |
l_returnflag | character(1) | | |
l_linestatus | character(1) | | |
l_shipdate | date | | |
l_commitdate | date | | |
l_receiptdate | date | | |
l_shipinstruct | character varying(25) | | |
l_shipmode | character varying(10) | | |
l_comment | character varying(44) | | |
Indexes:
"lineitem_pkey" PRIMARY KEY, btree (l_orderkey, l_linenumber)
"idx_lineitem_orderkey" btree (l_orderkey)
"idx_lineitem_part_supp" btree (l_partkey, l_suppkey)
"idx_lineitem_shipdate" btree (l_shipdate, l_discount, l_quantity)
Foreign-key constraints:
"lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)
Table "public.part"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+-----------------------------------------
p_partkey | integer | | not null | nextval('part_p_partkey_seq'::regclass)
p_name | character varying(55) | | |
p_mfgr | character(25) | | |
p_brand | character(10) | | |
p_type | character varying(25) | | |
p_size | integer | | |
p_container | character(10) | | |
p_retailprice | numeric | | |
p_comment | character varying(23) | | |
Indexes:
"part_pkey" PRIMARY KEY, btree (p_partkey)
Table "public.partsupp"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
ps_partkey | integer | | not null |
ps_suppkey | integer | | not null |
ps_availqty | integer | | |
ps_supplycost | numeric | | |
ps_comment | character varying(199) | | |
Indexes:
"partsupp_pkey" PRIMARY KEY, btree (ps_partkey, ps_suppkey)
"idx_partsupp_partkey" btree (ps_partkey)
"idx_partsupp_suppkey" btree (ps_suppkey)
Foreign-key constraints:
"partsupp_ps_suppkey_fkey" FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey)
explain analyze select
ps_suppkey
from
partsupp,
(
select
l_partkey agg_partkey,
l_suppkey agg_suppkey
from
lineitem
group by
l_partkey,
l_suppkey
) agg_lineitem
where
agg_partkey = ps_partkey
and agg_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=172852.62..2123144.13 rows=524 width=4) (actual time=494.369..17436.567 rows=86801 loops=1)
Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
-> Group (cost=0.56..1857642.81 rows=5998611 width=8) (actual time=0.076..16487.350 rows=7995644 loops=1)
Group Key: lineitem.l_partkey, lineitem.l_suppkey
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..1557712.25 rows=59986112 width=8) (actual time=0.074..12144.346 rows=59983724 loops=1)
Heap Fetches: 0
-> Sort (cost=172852.06..173741.94 rows=355951 width=12) (actual time=493.214..509.059 rows=86836 loops=1)
Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
Sort Method: quicksort Memory: 7143kB
-> Nested Loop (cost=0.43..140031.03 rows=355951 width=12) (actual time=0.083..470.353 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.024..356.293 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.49 rows=18 width=8) (actual time=0.004..0.005 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
Heap Fetches: 0
Planning time: 1.154 ms
Execution time: 17439.543 ms
(18 rows)
explain analyze select
ps_suppkey
from
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=222348.75..474275.19 rows=20 width=4) (actual time=855.417..2294.177 rows=650135 loops=1)
Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
-> Sort (cost=222348.00..223743.48 rows=558193 width=12) (actual time=855.211..902.610 rows=650135 loops=1)
Sort Key: lineitem.l_partkey, lineitem.l_suppkey
Sort Method: quicksort Memory: 55052kB
-> Nested Loop (cost=0.56..169067.35 rows=558193 width=12) (actual time=0.053..710.828 rows=650135 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.031..369.215 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..4.82 rows=28 width=8) (actual time=0.005..0.013 rows=30 loops=21709)
Index Cond: (l_partkey = part.p_partkey)
Heap Fetches: 0
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..207747.79 rows=7999957 width=8) (actual time=0.116..780.472 rows=8563023 loops=1)
Heap Fetches: 0
Planning time: 2.048 ms
Execution time: 2314.168 ms
(16 rows)
explain analyze select
*
from
part,
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey = p_partkey
and p_name like 'hot%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..667975.85 rows=22 width=372) (actual time=0.061..1673.810 rows=650135 loops=1)
-> Nested Loop (cost=0.43..217507.23 rows=80809 width=274) (actual time=0.045..377.498 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=130) (actual time=0.031..268.704 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..7.32 rows=18 width=144) (actual time=0.003..0.004 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.56..5.56 rows=1 width=98) (actual time=0.003..0.013 rows=7 loops=86836)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Planning time: 1.725 ms
Execution time: 1691.573 ms
(11 rows)
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
l_orderkey | integer | | not null |
l_partkey | integer | | not null |
l_suppkey | integer | | not null |
l_linenumber | integer | | not null |
l_quantity | numeric | | |
l_extendedprice | numeric | | |
l_discount | numeric | | |
l_tax | numeric | | |
l_returnflag | character(1) | | |
l_linestatus | character(1) | | |
l_shipdate | date | | |
l_commitdate | date | | |
l_receiptdate | date | | |
l_shipinstruct | character varying(25) | | |
l_shipmode | character varying(10) | | |
l_comment | character varying(44) | | |
Indexes:
"lineitem_pkey" PRIMARY KEY, btree (l_orderkey, l_linenumber)
"idx_lineitem_orderkey" btree (l_orderkey)
"idx_lineitem_part_supp" btree (l_partkey, l_suppkey)
"idx_lineitem_shipdate" btree (l_shipdate, l_discount, l_quantity)
Foreign-key constraints:
"lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)
"lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)
Table "public.part"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+-----------------------------------------
p_partkey | integer | | not null | nextval('part_p_partkey_seq'::regclass)
p_name | character varying(55) | | |
p_mfgr | character(25) | | |
p_brand | character(10) | | |
p_type | character varying(25) | | |
p_size | integer | | |
p_container | character(10) | | |
p_retailprice | numeric | | |
p_comment | character varying(23) | | |
Indexes:
"part_pkey" PRIMARY KEY, btree (p_partkey)
Table "public.partsupp"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
ps_partkey | integer | | not null |
ps_suppkey | integer | | not null |
ps_availqty | integer | | |
ps_supplycost | numeric | | |
ps_comment | character varying(199) | | |
Indexes:
"partsupp_pkey" PRIMARY KEY, btree (ps_partkey, ps_suppkey)
"idx_partsupp_partkey" btree (ps_partkey)
"idx_partsupp_suppkey" btree (ps_suppkey)
Foreign-key constraints:
"partsupp_ps_suppkey_fkey" FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey)
Referenced by:
TABLE "lineitem" CONSTRAINT "lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)
explain analyze select
ps_suppkey
from
partsupp,
(
select
l_partkey agg_partkey,
l_suppkey agg_suppkey
from
lineitem
group by
l_partkey,
l_suppkey
) agg_lineitem
where
agg_partkey = ps_partkey
and agg_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=172852.62..2123144.13 rows=524 width=4) (actual time=322.828..17396.426 rows=86801 loops=1)
Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
-> Group (cost=0.56..1857642.81 rows=5998611 width=8) (actual time=0.021..16605.063 rows=7995644 loops=1)
Group Key: lineitem.l_partkey, lineitem.l_suppkey
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..1557712.25 rows=59986112 width=8) (actual time=0.020..12279.866 rows=59983724 loops=1)
Heap Fetches: 0
-> Sort (cost=172852.06..173741.94 rows=355951 width=12) (actual time=321.998..334.440 rows=86836 loops=1)
Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
Sort Method: quicksort Memory: 7143kB
-> Nested Loop (cost=0.43..140031.03 rows=355951 width=12) (actual time=0.025..303.145 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.011..237.792 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.49 rows=18 width=8) (actual time=0.002..0.003 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
Heap Fetches: 0
Planning time: 0.346 ms
Execution time: 17398.979 ms
(18 rows)
explain analyze select
ps_suppkey
from
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=222348.75..474275.19 rows=558193 width=4) (actual time=840.122..2124.006 rows=650135 loops=1)
Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
-> Sort (cost=222348.00..223743.48 rows=558193 width=12) (actual time=840.055..882.144 rows=650135 loops=1)
Sort Key: lineitem.l_partkey, lineitem.l_suppkey
Sort Method: quicksort Memory: 55052kB
-> Nested Loop (cost=0.56..169067.35 rows=558193 width=12) (actual time=0.049..696.759 rows=650135 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.027..379.221 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..4.82 rows=28 width=8) (actual time=0.005..0.012 rows=30 loops=21709)
Index Cond: (l_partkey = part.p_partkey)
Heap Fetches: 0
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..207747.79 rows=7999957 width=8) (actual time=0.029..716.343 rows=8563023 loops=1)
Heap Fetches: 0
Planning time: 2.223 ms
Execution time: 2141.746 ms
(16 rows)
explain analyze select
*
from
part,
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey = p_partkey
and p_name like 'hot%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..667975.85 rows=605930 width=372) (actual time=0.061..1739.609 rows=650135 loops=1)
-> Nested Loop (cost=0.43..217507.23 rows=80809 width=274) (actual time=0.041..495.938 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=130) (actual time=0.025..364.500 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..7.32 rows=18 width=144) (actual time=0.004..0.005 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.56..5.56 rows=1 width=98) (actual time=0.004..0.011 rows=7 loops=86836)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Planning time: 1.836 ms
Execution time: 1761.390 ms
(11 rows)
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY);
ALTER TABLE
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
l_orderkey | integer | | not null |
l_partkey | integer | | not null |
l_suppkey | integer | | not null |
l_linenumber | integer | | not null |
l_quantity | numeric | | |
l_extendedprice | numeric | | |
l_discount | numeric | | |
l_tax | numeric | | |
l_returnflag | character(1) | | |
l_linestatus | character(1) | | |
l_shipdate | date | | |
l_commitdate | date | | |
l_receiptdate | date | | |
l_shipinstruct | character varying(25) | | |
l_shipmode | character varying(10) | | |
l_comment | character varying(44) | | |
Indexes:
"lineitem_pkey" PRIMARY KEY, btree (l_orderkey, l_linenumber)
"idx_lineitem_orderkey" btree (l_orderkey)
"idx_lineitem_part_supp" btree (l_partkey, l_suppkey)
"idx_lineitem_shipdate" btree (l_shipdate, l_discount, l_quantity)
Foreign-key constraints:
"lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)
"lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)
Table "public.part"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+-----------------------------------------
p_partkey | integer | | not null | nextval('part_p_partkey_seq'::regclass)
p_name | character varying(55) | | |
p_mfgr | character(25) | | |
p_brand | character(10) | | |
p_type | character varying(25) | | |
p_size | integer | | |
p_container | character(10) | | |
p_retailprice | numeric | | |
p_comment | character varying(23) | | |
Indexes:
"part_pkey" PRIMARY KEY, btree (p_partkey)
Referenced by:
TABLE "partsupp" CONSTRAINT "partsupp_ps_partkey_fkey" FOREIGN KEY (ps_partkey) REFERENCES part(p_partkey)
Table "public.partsupp"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
ps_partkey | integer | | not null |
ps_suppkey | integer | | not null |
ps_availqty | integer | | |
ps_supplycost | numeric | | |
ps_comment | character varying(199) | | |
Indexes:
"partsupp_pkey" PRIMARY KEY, btree (ps_partkey, ps_suppkey)
"idx_partsupp_partkey" btree (ps_partkey)
"idx_partsupp_suppkey" btree (ps_suppkey)
Foreign-key constraints:
"partsupp_ps_partkey_fkey" FOREIGN KEY (ps_partkey) REFERENCES part(p_partkey)
"partsupp_ps_suppkey_fkey" FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey)
Referenced by:
TABLE "lineitem" CONSTRAINT "lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)
explain analyze select
ps_suppkey
from
partsupp,
(
select
l_partkey agg_partkey,
l_suppkey agg_suppkey
from
lineitem
group by
l_partkey,
l_suppkey
) agg_lineitem
where
agg_partkey = ps_partkey
and agg_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=146618.43..2094846.01 rows=11 width=4) (actual time=563.410..17588.061 rows=86801 loops=1)
Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
-> Group (cost=0.56..1857642.81 rows=5998611 width=8) (actual time=0.028..16557.915 rows=7995644 loops=1)
Group Key: lineitem.l_partkey, lineitem.l_suppkey
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..1557712.25 rows=59986112 width=8) (actual time=0.026..12232.928 rows=59983724 loops=1)
Heap Fetches: 0
-> Sort (cost=146617.86..146819.89 rows=80809 width=12) (actual time=562.513..575.599 rows=86836 loops=1)
Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
Sort Method: quicksort Memory: 7143kB
-> Nested Loop (cost=0.43..140031.03 rows=80809 width=12) (actual time=0.054..536.003 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.028..441.543 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.49 rows=18 width=8) (actual time=0.003..0.004 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
Heap Fetches: 0
Planning time: 0.717 ms
Execution time: 17590.623 ms
(18 rows)
explain analyze select
ps_suppkey
from
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey in (
select
p_partkey
from
part
where
p_name like 'hot%'
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..388884.01 rows=605928 width=4) (actual time=0.039..806.723 rows=650135 loops=1)
-> Nested Loop (cost=0.43..141123.03 rows=80809 width=12) (actual time=0.030..429.537 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.020..356.212 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.54 rows=18 width=8) (actual time=0.003..0.003 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
Heap Fetches: 0
-> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..3.06 rows=1 width=8) (actual time=0.002..0.004 rows=7 loops=86836)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Heap Fetches: 0
Planning time: 0.845 ms
Execution time: 823.987 ms
(13 rows)
explain analyze select
*
from
part,
partsupp,
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and ps_partkey = p_partkey
and p_name like 'hot%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..667975.85 rows=605930 width=372) (actual time=0.060..1675.235 rows=650135 loops=1)
-> Nested Loop (cost=0.43..217507.23 rows=80809 width=274) (actual time=0.041..461.204 rows=86836 loops=1)
-> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=130) (actual time=0.025..331.150 rows=21709 loops=1)
Filter: ((p_name)::text ~~ 'hot%'::text)
Rows Removed by Filter: 1978291
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..7.32 rows=18 width=144) (actual time=0.004..0.005 rows=4 loops=21709)
Index Cond: (ps_partkey = part.p_partkey)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.56..5.56 rows=1 width=98) (actual time=0.004..0.011 rows=7 loops=86836)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
Planning time: 1.678 ms
Execution time: 1697.085 ms
(11 rows)
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
