Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218]) by postgresql.org (Postfix) with ESMTP id 9AC5E47585D; Wed, 17 Jul 2002 06:12:47 -0400 (EDT) Received: from ALIGOTE (aligote [193.144.185.202]) by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6HACjb09428; Wed, 17 Jul 2002 12:12:45 +0200 Message-ID: <01b901c22d7b$2e9e9f90$cab990c1@atc.unican.es> From: "Luis Alberto Amigo Navarro" To: , Subject: why is postgres estimating so badly? Date: Wed, 17 Jul 2002 12:17:40 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01B6_01C22D8B.F20122E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200207/262 X-Sequence-Number: 8640 This is a multi-part message in MIME format. ------=_NextPart_000_01B6_01C22D8B.F20122E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a query and estimations and results don=B4t look similar, here is ex= plain analyze: NOTICE: QUERY PLAN: Sort (cost=3D12443.90..12443.90 rows=3D1 width=3D93) (actual time=3D505331= .94..505332.67 rows=3D175 loops=3D1) -> Aggregate (cost=3D12443.88..12443.89 rows=3D1 width=3D93) (actual ti= me=3D472520.29..505326.48 rows=3D175 loops=3D1) -> Group (cost=3D12443.88..12443.89 rows=3D1 width=3D93) (actual = time=3D472307.31..485173.92 rows=3D325302 loops=3D1) -> Sort (cost=3D12443.88..12443.88 rows=3D1 width=3D93) (ac= tual time=3D472307.24..473769.79 rows=3D325302 loops=3D1) -> Nested Loop (cost=3D12439.25..12443.87 rows=3D1 wi= dth=3D93) (actual time=3D103787.68..441614.43 rows=3D325302 loops=3D1) -> Hash Join (cost=3D12439.25..12440.64 rows=3D= 1 width=3D85) (actual time=3D103733.76..120916.86 rows=3D325302 loops=3D1) -> Seq Scan on nation (cost=3D0.00..1.25 = rows=3D25 width=3D15) (actual time=3D7.81..8.72 rows=3D25 loops=3D1) -> Hash (cost=3D12439.25..12439.25 rows= =3D1 width=3D70) (actual time=3D103722.25..103722.25 rows=3D0 loops=3D1) -> Nested Loop (cost=3D0.00..12439.= 25 rows=3D1 width=3D70) (actual time=3D95.43..100162.91 rows=3D325302 loops= =3D1) -> Nested Loop (cost=3D0.00..= 12436.23 rows=3D1 width=3D62) (actual time=3D84.91..47502.93 rows=3D325302 = loops=3D1) -> Nested Loop (cost=3D= 0.00..12412.93 rows=3D4 width=3D24) (actual time=3D66.86..8806.01 rows=3D43= 424 loops=3D1) -> Seq Scan on par= t (cost=3D0.00..12399.00 rows=3D1 width=3D4) (actual time=3D24.88..4076.81= rows=3D10856 loops=3D1) -> Index Scan usin= g partsupp_pkey on partsupp (cost=3D0.00..13.89 rows=3D4 width=3D20) (actu= al time=3D0.20..0.34 rows=3D4 loops=3D10856) -> Index Scan using l_pa= rtsupp_index on lineitem (cost=3D0.00..6.02 rows=3D1 width=3D38) (actual t= ime=3D0.20..0.61 rows=3D7 loops=3D43424) -> Index Scan using supplier_p= key on supplier (cost=3D0.00..3.01 rows=3D1 width=3D8) (actual time=3D0.08= ..0.10 rows=3D1 loops=3D325302) -> Index Scan using orders_pkey on orders (cost= =3D0.00..3.22 rows=3D1 width=3D8) (actual time=3D0.85..0.87 rows=3D1 loops= =3D325302) Total runtime: 505563.85 msec estimated 12000msec here is the query: SELECT nation, o_year, CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit FROM( SELECT nation.name AS nation, EXTRACT(year FROM orders.orderdate) AS o_year, lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem= .quantity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE supplier.suppkey=3Dlineitem.suppkey AND partsupp.suppkey=3Dlineitem.suppkey AND partsupp.partkey=3Dlineitem.partkey AND part.partkey=3Dlineitem.partkey AND orders.orderkey=3Dlineitem.orderkey AND supplier.nationkey=3Dnation.nationkey AND part.name LIKE '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC; lineitem is about 6M rows partsupp 800K rows part 200K rows any advice? Thanks and regards ------=_NextPart_000_01B6_01C22D8B.F20122E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have a query and estimations and results= don=B4t=20 look similar, here is explain analyze:
 
 NOTICE:  QUERY PLAN:
 
Sort  (cost=3D12443.90..12443.90 rows= =3D1=20 width=3D93) (actual time=3D505331.94..505332.67 rows=3D175 loops=3D1)
&n= bsp;=20 ->  Aggregate  (cost=3D12443.88..12443.89 rows=3D1 width=3D93)= (actual=20 time=3D472520.29..505326.48 rows=3D175=20 loops=3D1)
        ->  Group&= nbsp;=20 (cost=3D12443.88..12443.89 rows=3D1 width=3D93) (actual time=3D472307.31..4= 85173.92=20 rows=3D325302=20 loops=3D1)
          &= nbsp;  =20 ->  Sort  (cost=3D12443.88..12443.88 rows=3D1 width=3D93) (act= ual=20 time=3D472307.24..473769.79 rows=3D325302=20 loops=3D1)
          &= nbsp;        =20 ->  Nested Loop  (cost=3D12439.25..12443.87 rows=3D1 width=3D9= 3) (actual=20 time=3D103787.68..441614.43 rows=3D325302=20 loops=3D1)
          &= nbsp;           &nbs= p;  =20 ->  Hash Join  (cost=3D12439.25..12440.64 rows=3D1 width=3D85)= (actual=20 time=3D103733.76..120916.86 rows=3D325302=20 loops=3D1)
          &= nbsp;           &nbs= p;        =20 ->  Seq Scan on nation  (cost=3D0.00..1.25 rows=3D25 width=3D1= 5) (actual=20 time=3D7.81..8.72 rows=3D25=20 loops=3D1)
          &= nbsp;           &nbs= p;        =20 ->  Hash  (cost=3D12439.25..12439.25 rows=3D1 width=3D70) (act= ual=20 time=3D103722.25..103722.25 rows=3D0=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp; =20 ->  Nested Loop  (cost=3D0.00..12439.25 rows=3D1 width=3D70) (= actual=20 time=3D95.43..100162.91 rows=3D325302=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;       =20 ->  Nested Loop  (cost=3D0.00..12436.23 rows=3D1 width=3D62) (= actual=20 time=3D84.91..47502.93 rows=3D325302=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p; =20 ->  Nested Loop  (cost=3D0.00..12412.93 rows=3D4 width=3D24) (= actual=20 time=3D66.86..8806.01 rows=3D43424=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;       =20 ->  Seq Scan on part  (cost=3D0.00..12399.00 rows=3D1 width=3D= 4) (actual=20 time=3D24.88..4076.81 rows=3D10856=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;       =20 ->  Index Scan using partsupp_pkey on partsupp  (cost=3D0.00..= 13.89=20 rows=3D4 width=3D20) (actual time=3D0.20..0.34 rows=3D4=20 loops=3D10856)
         &nb= sp;            =             &nb= sp;            =   =20 ->  Index Scan using l_partsupp_index on lineitem  (cost=3D0.0= 0..6.02=20 rows=3D1 width=3D38) (actual time=3D0.20..0.61 rows=3D7=20 loops=3D43424)
         &nb= sp;            =             &nb= sp;        =20 ->  Index Scan using supplier_pkey on supplier  (cost=3D0.00..= 3.01=20 rows=3D1 width=3D8) (actual time=3D0.08..0.10 rows=3D1=20 loops=3D325302)
         &n= bsp;            = ;   =20 ->  Index Scan using orders_pkey on orders  (cost=3D0.00..3.22= rows=3D1=20 width=3D8) (actual time=3D0.85..0.87 rows=3D1 loops=3D325302)
Total runt= ime:=20 505563.85 msec
estimated 12000msec
 
here is the query:
SELECT
 nation,
 o_year,
 CAST((sum(amount= ))AS=20 NUMERIC(10,2))AS sum_profit
FROM(
 SELECT
  nation.= name=20 AS nation,
  EXTRACT(year FROM orders.orderdate) AS=20 o_year,
  lineitem.extendedprice*(1-lineitem.discount)-partsup= p.supplycost*lineitem.quantity=20 AS=20 amount
 FROM
  part,
  supplier,
 = ; lineitem,
  partsupp,
  orders,
 &= nbsp;nation
 WHERE
  supplier.suppkey=3Dlineitem.suppk= ey
  AND=20 partsupp.suppkey=3Dlineitem.suppkey
  AND=20 partsupp.partkey=3Dlineitem.partkey
  AND=20 part.partkey=3Dlineitem.partkey
  AND=20 orders.orderkey=3Dlineitem.orderkey
  AND=20 supplier.nationkey=3Dnation.nationkey
  AND part.name LIKE=20 '%green%'
 ) AS profit
GROUP=20 BY
 nation,
 o_year
ORDER BY
 nation,
 o= _year=20 DESC;
lineitem is about 6M rows
partsupp 800K rows
part 200K rows
 
any advice?
Thanks and regards
 
 
------=_NextPart_000_01B6_01C22D8B.F20122E0--