X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6C6519FA4A8 for ; Fri, 21 Jul 2006 15:46:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 05555-10 for ; Fri, 21 Jul 2006 15:46:15 -0300 (ADT) X-Greylist: delayed 00:30:38.973785 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6C8A59FA68D for ; Fri, 21 Jul 2006 15:46:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from stormtide.ca (stormtide.ca [69.61.78.222]) by svr4.postgresql.org (Postfix) with ESMTP id 0D7B65AF06C for ; Fri, 21 Jul 2006 18:15:34 +0000 (GMT) Received: by stormtide.ca (Postfix, from userid 55) id 61274B9506; Fri, 21 Jul 2006 12:15:31 -0600 (MDT) Received: from kdesktop (S010600131042729b.ed.shawcable.net [68.148.109.102]) by stormtide.ca (Postfix) with ESMTP id F11B4B80AC for ; Fri, 21 Jul 2006 12:15:24 -0600 (MDT) Message-ID: <003b01c6acf1$a66d8910$0701a8c0@kdesktop> From: "Kevin McArthur" To: Subject: Bad Planner Statistics for Uneven distribution. Date: Fri, 21 Jul 2006 12:15:28 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0038_01C6ACBF.5A5F3DC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2670 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200607/212 X-Sequence-Number: 20072 This is a multi-part message in MIME format. ------=_NextPart_000_0038_01C6ACBF.5A5F3DC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I discussed this with a few members of #postgresql freenode this = morning. I'll keep it breif; [note: i have cleaned out columns not = relevant] I have two tables, brands and models_brands. The first has about 300 = records, the later about 350,000 records. The number of distinct brands = in the models_brands table is < 10. =3D# \d models_brands Table "public.models_brands" Column | Type | Modifiers --------+-----------------------+----------- model | integer | not null brand | integer | not null Indexes: "models_brands_brand" btree (brand) Foreign-key constraints: "models_brands_brand_fkey" FOREIGN KEY (brand) REFERENCES = brands(brand_id) ON UPDATE CASCADE ON DELETE CASCADE "models_brands_model_fkey" FOREIGN KEY (model) REFERENCES = models(model_id) ON UPDATE CASCADE ON DELETE CASCADE a=3D# \d brands; Table "public.brands" Column | Type | Modifiers ------------+------------------------+-----------------------------------= ------------------------ brand_id | integer | not null default = nextval('brands_brand_id_seq'::regclass) brand_name | character varying(255) | not null Indexes: "brands_pkey" PRIMARY KEY, btree (brand_id) Now the plans/problems.. =3D# set enable_seqscan to on; SET =3D# explain analyze select distinct brand from models_brands; QUERY PLAN -------------------------------------------------------------------------= ---------------------------------------------------------- Unique (cost=3D46300.70..48148.15 rows=3D4 width=3D4) (actual = time=3D3699.691..6215.216 rows=3D4 loops=3D1) -> Sort (cost=3D46300.70..47224.43 rows=3D369489 width=3D4) (actual = time=3D3699.681..5027.069 rows=3D369489 loops=3D1) Sort Key: brand -> Seq Scan on models_brands (cost=3D0.00..6411.89 = rows=3D369489 width=3D4) (actual time=3D0.040..1352.997 rows=3D369489 = loops=3D1) Total runtime: 6223.666 ms (5 rows) =3D# set enable_seqscan to off; SET =3D# explain analyze select distinct brand from models_brands; = QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------- Unique (cost=3D0.00..863160.68 rows=3D4 width=3D4) (actual = time=3D0.131..2584.779 rows=3D4 loops=3D1) -> Index Scan using models_brands_brand on models_brands = (cost=3D0.00..862236.96 rows=3D369489 width=3D4) (actual = time=3D0.122..1440.809 rows=3D369489 loops=3D1) Total runtime: 2584.871 ms (3 rows) Picks the wrong plan here. Should pick the index with seqscanning = enabled. More (as a different wording/query)... (as suggested by others on irc) =3D# set enable_seqscan to on; SET =3D# explain analyze select brand_id from brands where exists (select 1 = from models_brands where brand =3D brands.brand_id); QUERY PLAN -------------------------------------------------------------------------= --------------------------------------------------- Seq Scan on brands (cost=3D0.00..30.09 rows=3D152 width=3D4) (actual = time=3D7742.460..62567.543 rows=3D4 loops=3D1) Filter: (subplan) SubPlan -> Seq Scan on models_brands (cost=3D0.00..7335.61 rows=3D92372 = width=3D0) (actual time=3D206.467..206.467 rows=3D0 loops=3D303) Filter: (brand =3D $0) Total runtime: 62567.626 ms a=3D# set enable_seqscan to off; SET =3D# explain analyze select brand_id from brands where exists (select 1 = from models_brands where brand =3D brands.brand_id); = QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= ---- Seq Scan on brands (cost=3D100000000.00..100000715.90 rows=3D152 = width=3D4) (actual time=3D0.615..3.710 rows=3D4 loops=3D1) Filter: (subplan) SubPlan -> Index Scan using models_brands_brand on models_brands = (cost=3D0.00..216410.97 rows=3D92372 width=3D0) (actual = time=3D0.008..0.008 rows=3D0 loops=3D303) Index Cond: (brand =3D $0) Total runtime: 3.790 ms It was also tried to similar results with a LIMIT 1 in the subquery for = exist. More... Seqscan still off.. =3D# explain analyze select distinct brand_id from brands inner join = models_brands on (brand_id =3D brand); = QUERY PLAN = =20 -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------- Unique (cost=3D0.00..867782.58 rows=3D303 width=3D4) (actual = time=3D0.391..4898.579 rows=3D4 loops=3D1) -> Merge Join (cost=3D0.00..866858.85 rows=3D369489 width=3D4) = (actual time=3D0.383..3749.771 rows=3D369489 loops=3D1) Merge Cond: ("outer".brand_id =3D "inner".brand) -> Index Scan using brands_pkey on brands (cost=3D0.00..15.53 = rows=3D303 width=3D4) (actual time=3D0.080..0.299 rows=3D60 loops=3D1) -> Index Scan using models_brands_brand on models_brands = (cost=3D0.00..862236.96 rows=3D369489 width=3D4) (actual = time=3D0.013..1403.175 rows=3D369489 loops=3D1) Total runtime: 4898.697 ms =3D# set enable_seqscan to on; SET =3D# explain analyze select distinct brand_id from brands inner join = models_brands on (brand_id =3D brand); QUERY = PLAN -------------------------------------------------------------------------= ---------------------------------------------------------------- Unique (cost=3D46300.70..52770.04 rows=3D303 width=3D4) (actual = time=3D3742.046..8560.833 rows=3D4 loops=3D1) -> Merge Join (cost=3D46300.70..51846.32 rows=3D369489 width=3D4) = (actual time=3D3742.035..7406.677 rows=3D369489 loops=3D1) Merge Cond: ("outer".brand_id =3D "inner".brand) -> Index Scan using brands_pkey on brands (cost=3D0.00..15.53 = rows=3D303 width=3D4) (actual time=3D0.077..0.407 rows=3D60 loops=3D1) -> Sort (cost=3D46300.70..47224.43 rows=3D369489 width=3D4) = (actual time=3D3741.584..5051.348 rows=3D369489 loops=3D1) Sort Key: models_brands.brand -> Seq Scan on models_brands (cost=3D0.00..6411.89 = rows=3D369489 width=3D4) (actual time=3D0.027..1346.178 rows=3D369489 = loops=3D1) Total runtime: 8589.502 ms (8 rows) Hope that helps Kevin McArthur ------=_NextPart_000_0038_01C6ACBF.5A5F3DC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I discussed this with a few members of = #postgresql=20 freenode this morning. I'll keep it breif; [note: i have cleaned out = columns not=20 relevant]
 
I have two tables, brands and = models_brands. The=20 first has about 300 records, the later about 350,000 records. The number = of=20 distinct brands in the models_brands table is < 10.
 
 
 
=3D# \d=20 models_brands
        Table=20 "public.models_brands"
 Column=20 |        =20 Type          |=20 Modifiers
--------+-----------------------+-----------
 model&= nbsp; |=20 integer           =    =20 | not null
 brand  |=20 integer           =    =20 | not null
Indexes:
    "models_brands_brand" btree = (brand)
Foreign-key constraints:
   =20 "models_brands_brand_fkey" FOREIGN KEY (brand) REFERENCES = brands(brand_id) ON=20 UPDATE CASCADE ON DELETE CASCADE
   =20 "models_brands_model_fkey" FOREIGN KEY (model) REFERENCES = models(model_id) ON=20 UPDATE CASCADE ON DELETE CASCADE
 
a=3D# \d=20 brands;
          &n= bsp;           &nb= sp;           &nbs= p;  =20 Table "public.brands"
   Column  =20 |         =20 Type         =20 |            =              = Modifiers
------------+------------------------+----------------------= -------------------------------------
 brand_id  =20 |=20 integer           =     =20 | not null default = nextval('brands_brand_id_seq'::regclass)
 brand_name=20 | character varying(255) | not null
Indexes:
   =20 "brands_pkey" PRIMARY KEY, btree (brand_id)
 
Now the plans/problems..
 
=3D# set enable_seqscan to = on;
SET
=3D# explain=20 analyze select distinct brand from=20 models_brands;
         &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ; =20 QUERY=20 PLAN
-----------------------------------------------------------------= ------------------------------------------------------------------
&nb= sp;Unique =20 (cost=3D46300.70..48148.15 rows=3D4 width=3D4) (actual = time=3D3699.691..6215.216 rows=3D4=20 loops=3D1)
   ->  Sort  = (cost=3D46300.70..47224.43=20 rows=3D369489 width=3D4) (actual time=3D3699.681..5027.069 rows=3D369489 = loops=3D1)
         Sort Key: = brand
         ->  = Seq Scan=20 on models_brands  (cost=3D0.00..6411.89 rows=3D369489 width=3D4) = (actual=20 time=3D0.040..1352.997 rows=3D369489 loops=3D1)
 Total runtime: = 6223.666=20 ms
(5 rows)
 
=3D# set enable_seqscan to = off;
SET
=3D# explain=20 analyze select distinct brand from=20 models_brands;
         &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =  =20 QUERY=20 PLAN
-----------------------------------------------------------------= -------------------------------------------------------------------------= -----------------
 Unique =20 (cost=3D0.00..863160.68 rows=3D4 width=3D4) (actual = time=3D0.131..2584.779 rows=3D4=20 loops=3D1)
   ->  Index Scan using = models_brands_brand on=20 models_brands  (cost=3D0.00..862236.96 rows=3D369489 width=3D4) = (actual=20 time=3D0.122..1440.809 rows=3D369489 loops=3D1)
 Total runtime: = 2584.871=20 ms
(3 rows)
 
 
Picks the wrong plan here. Should pick = the index=20 with seqscanning enabled.
 
 
More (as a different wording/query)... = (as=20 suggested by others on irc)
 
 
=3D# set enable_seqscan to = on;
SET
=3D# explain=20 analyze select brand_id from brands where exists (select 1 from = models_brands=20 where brand =3D=20 brands.brand_id);
        &nbs= p;            = ;            =             &= nbsp;          =20 QUERY=20 PLAN
-----------------------------------------------------------------= -----------------------------------------------------------
 Seq = Scan on brands  (cost=3D0.00..30.09 rows=3D152 width=3D4) (actual=20 time=3D7742.460..62567.543 rows=3D4 loops=3D1)
   Filter:=20 (subplan)
   SubPlan
     = ->  Seq=20 Scan on models_brands  (cost=3D0.00..7335.61 rows=3D92372 = width=3D0) (actual=20 time=3D206.467..206.467 rows=3D0=20 loops=3D303)
         &nb= sp;=20 Filter: (brand =3D $0)
 Total runtime: 62567.626 ms
 
a=3D# set enable_seqscan to = off;
SET
=3D# explain analyze select brand_id = from brands=20 where exists (select 1 from models_brands where brand =3D=20 brands.brand_id);
        &nbs= p;            = ;            =             &= nbsp;           &n= bsp;           =20 QUERY=20 PLAN
-----------------------------------------------------------------= -------------------------------------------------------------------------= ------------
 Seq=20 Scan on brands  (cost=3D100000000.00..100000715.90 rows=3D152 = width=3D4) (actual=20 time=3D0.615..3.710 rows=3D4 loops=3D1)
   Filter:=20 (subplan)
   SubPlan
     = ->  Index=20 Scan using models_brands_brand on models_brands  = (cost=3D0.00..216410.97=20 rows=3D92372 width=3D0) (actual time=3D0.008..0.008 rows=3D0=20 loops=3D303)
         &nb= sp; Index=20 Cond: (brand =3D $0)
 Total runtime: 3.790 ms
 
 
It was also tried to similar results = with a LIMIT 1=20 in the subquery for exist.
 
More...
 
Seqscan still off..
 
 
=3D# explain analyze select distinct = brand_id from=20 brands inner join models_brands on (brand_id =3D=20 brand);
          &n= bsp;           &nb= sp;           &nbs= p;            = ;            =             &= nbsp;  =20 QUERY=20 PLAN           &nb= sp;           &nbs= p;            = ;            =             &= nbsp;          =20
---------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------
 Unique =20 (cost=3D0.00..867782.58 rows=3D303 width=3D4) (actual = time=3D0.391..4898.579 rows=3D4=20 loops=3D1)
   ->  Merge Join  = (cost=3D0.00..866858.85=20 rows=3D369489 width=3D4) (actual time=3D0.383..3749.771 rows=3D369489=20 loops=3D1)
         Merge = Cond:=20 ("outer".brand_id =3D=20 "inner".brand)
         = -> =20 Index Scan using brands_pkey on brands  (cost=3D0.00..15.53 = rows=3D303 width=3D4)=20 (actual time=3D0.080..0.299 rows=3D60=20 loops=3D1)
         = ->  Index=20 Scan using models_brands_brand on models_brands  = (cost=3D0.00..862236.96=20 rows=3D369489 width=3D4) (actual time=3D0.013..1403.175 rows=3D369489=20 loops=3D1)
 Total runtime: 4898.697 ms
 
=3D# set enable_seqscan to = on;
SET
=3D# explain=20 analyze select distinct brand_id from brands inner join models_brands on = (brand_id =3D=20 brand);
          &n= bsp;           &nb= sp;           &nbs= p;            = ;            =    =20 QUERY=20 PLAN
-----------------------------------------------------------------= ------------------------------------------------------------------------<= BR> Unique =20 (cost=3D46300.70..52770.04 rows=3D303 width=3D4) (actual = time=3D3742.046..8560.833=20 rows=3D4 loops=3D1)
   ->  Merge Join =20 (cost=3D46300.70..51846.32 rows=3D369489 width=3D4) (actual = time=3D3742.035..7406.677=20 rows=3D369489 = loops=3D1)
         Merge=20 Cond: ("outer".brand_id =3D=20 "inner".brand)
         = -> =20 Index Scan using brands_pkey on brands  (cost=3D0.00..15.53 = rows=3D303 width=3D4)=20 (actual time=3D0.077..0.407 rows=3D60=20 loops=3D1)
         = -> =20 Sort  (cost=3D46300.70..47224.43 rows=3D369489 width=3D4) (actual=20 time=3D3741.584..5051.348 rows=3D369489=20 loops=3D1)
          = ;    =20 Sort Key:=20 models_brands.brand
        &n= bsp;     =20 ->  Seq Scan on models_brands  (cost=3D0.00..6411.89 = rows=3D369489=20 width=3D4) (actual time=3D0.027..1346.178 rows=3D369489 = loops=3D1)
 Total=20 runtime: 8589.502 ms
(8 rows)
 
 
Hope that helps
 
Kevin McArthur
 
 
 
 
 
 
 
------=_NextPart_000_0038_01C6ACBF.5A5F3DC0--