Bad Planner Statistics for Uneven distribution.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
 
 
 
=# \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=# \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..
 
=# set enable_seqscan to on;
SET
=# explain analyze select distinct brand from models_brands;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=46300.70..48148.15 rows=4 width=4) (actual time=3699.691..6215.216 rows=4 loops=1)
   ->  Sort  (cost=46300.70..47224.43 rows=369489 width=4) (actual time=3699.681..5027.069 rows=369489 loops=1)
         Sort Key: brand
         ->  Seq Scan on models_brands  (cost=0.00..6411.89 rows=369489 width=4) (actual time=0.040..1352.997 rows=369489 loops=1)
 Total runtime: 6223.666 ms
(5 rows)
 
=# set enable_seqscan to off;
SET
=# explain analyze select distinct brand from models_brands;
                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.00..863160.68 rows=4 width=4) (actual time=0.131..2584.779 rows=4 loops=1)
   ->  Index Scan using models_brands_brand on models_brands  (cost=0.00..862236.96 rows=369489 width=4) (actual time=0.122..1440.809 rows=369489 loops=1)
 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)
 
 
=# set enable_seqscan to on;
SET
=# explain analyze select brand_id from brands where exists (select 1 from models_brands where brand = brands.brand_id);
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on brands  (cost=0.00..30.09 rows=152 width=4) (actual time=7742.460..62567.543 rows=4 loops=1)
   Filter: (subplan)
   SubPlan
     ->  Seq Scan on models_brands  (cost=0.00..7335.61 rows=92372 width=0) (actual time=206.467..206.467 rows=0 loops=303)
           Filter: (brand = $0)
 Total runtime: 62567.626 ms
 
a=# set enable_seqscan to off;
SET
=# explain analyze select brand_id from brands where exists (select 1 from models_brands where brand = brands.brand_id);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on brands  (cost=100000000.00..100000715.90 rows=152 width=4) (actual time=0.615..3.710 rows=4 loops=1)
   Filter: (subplan)
   SubPlan
     ->  Index Scan using models_brands_brand on models_brands  (cost=0.00..216410.97 rows=92372 width=0) (actual time=0.008..0.008 rows=0 loops=303)
           Index Cond: (brand = $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..
 
 
=# explain analyze select distinct brand_id from brands inner join models_brands on (brand_id = brand);
                                                                           QUERY PLAN                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.00..867782.58 rows=303 width=4) (actual time=0.391..4898.579 rows=4 loops=1)
   ->  Merge Join  (cost=0.00..866858.85 rows=369489 width=4) (actual time=0.383..3749.771 rows=369489 loops=1)
         Merge Cond: ("outer".brand_id = "inner".brand)
         ->  Index Scan using brands_pkey on brands  (cost=0.00..15.53 rows=303 width=4) (actual time=0.080..0.299 rows=60 loops=1)
         ->  Index Scan using models_brands_brand on models_brands  (cost=0.00..862236.96 rows=369489 width=4) (actual time=0.013..1403.175 rows=369489 loops=1)
 Total runtime: 4898.697 ms
 
=# set enable_seqscan to on;
SET
=# explain analyze select distinct brand_id from brands inner join models_brands on (brand_id = brand);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=46300.70..52770.04 rows=303 width=4) (actual time=3742.046..8560.833 rows=4 loops=1)
   ->  Merge Join  (cost=46300.70..51846.32 rows=369489 width=4) (actual time=3742.035..7406.677 rows=369489 loops=1)
         Merge Cond: ("outer".brand_id = "inner".brand)
         ->  Index Scan using brands_pkey on brands  (cost=0.00..15.53 rows=303 width=4) (actual time=0.077..0.407 rows=60 loops=1)
         ->  Sort  (cost=46300.70..47224.43 rows=369489 width=4) (actual time=3741.584..5051.348 rows=369489 loops=1)
               Sort Key: models_brands.brand
               ->  Seq Scan on models_brands  (cost=0.00..6411.89 rows=369489 width=4) (actual time=0.027..1346.178 rows=369489 loops=1)
 Total runtime: 8589.502 ms
(8 rows)
 
 
Hope that helps
 
Kevin McArthur
 
 
 
 
 

 
 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux