Skip site navigation (1) Skip section navigation (2)

Re: Queries 15 times slower on 8.1 beta 2 than on 8.0

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org,Jean-Pierre Pelletier <pelletier_32(at)sympatico(dot)ca>,John Arbash Meinel <john(at)arbash-meinel(dot)com>
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Date: 2005-09-22 22:54:32
Message-ID: 20050922225432.GA25103@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Sep 22, 2005 at 03:19:05PM -0700, Josh Berkus wrote:
> > "  ->  Seq Scan on price p  (cost=0.00..11317.75 rows=581475 width=4)
> > (actual time=0.004..1143.720 rows=581475 loops=1)"
> 
> Well, this is your pain point.   Can we see the index scan plan on 8.1?  
> Given that it's *expecting* only one row, I can't understand why it's 
> using a seq scan ...

I've created a simplified, self-contained test case for this:

CREATE TABLE price (
    priceid  integer PRIMARY KEY
);

CREATE TABLE supplier (
    supplierid  integer PRIMARY KEY
);

CREATE TABLE content (
    contentid   integer PRIMARY KEY,
    supplierid  integer NOT NULL REFERENCES supplier,
    priceid     integer NOT NULL REFERENCES price
);

INSERT INTO price (priceid) SELECT * FROM generate_series(1, 50000);
INSERT INTO supplier (supplierid) SELECT * FROM generate_series(1, 10000);
INSERT INTO content (contentid, supplierid, priceid) VALUES (1, 1, 50000);

ANALYZE price;
ANALYZE supplier;
ANALYZE content;

EXPLAIN ANALYZE
SELECT 0
FROM content c
LEFT OUTER JOIN supplier s ON c.supplierid = s.supplierid
LEFT OUTER JOIN price p    ON c.priceid = p.priceid;

Here's the EXPLAIN ANALYZE from 8.0.3:

 Nested Loop Left Join  (cost=0.00..7.06 rows=1 width=0) (actual time=0.180..0.232 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=0.00..4.04 rows=1 width=4) (actual time=0.105..0.133 rows=1 loops=1)
         ->  Seq Scan on content c  (cost=0.00..1.01 rows=1 width=8) (actual time=0.021..0.029 rows=1 loops=1)
         ->  Index Scan using supplier_pkey on supplier s  (cost=0.00..3.01 rows=1 width=4) (actual time=0.052..0.059 rows=1 loops=1)
               Index Cond: ("outer".supplierid = s.supplierid)
   ->  Index Scan using price_pkey on price p  (cost=0.00..3.01 rows=1 width=4) (actual time=0.046..0.055 rows=1 loops=1)
         Index Cond: ("outer".priceid = p.priceid)
 Total runtime: 0.582 ms

Here it is from 8.1beta2:

 Merge Right Join  (cost=4.05..1054.06 rows=1 width=0) (actual time=676.863..676.895 rows=1 loops=1)
   Merge Cond: ("outer".priceid = "inner".priceid)
   ->  Index Scan using price_pkey on price p  (cost=0.00..925.00 rows=50000 width=4) (actual time=0.035..383.345 rows=50000 loops=1)
   ->  Sort  (cost=4.05..4.05 rows=1 width=4) (actual time=0.152..0.159 rows=1 loops=1)
         Sort Key: c.priceid
         ->  Nested Loop Left Join  (cost=0.00..4.04 rows=1 width=4) (actual time=0.082..0.111 rows=1 loops=1)
               ->  Seq Scan on content c  (cost=0.00..1.01 rows=1 width=8) (actual time=0.016..0.024 rows=1 loops=1)
               ->  Index Scan using supplier_pkey on supplier s  (cost=0.00..3.01 rows=1 width=4) (actual time=0.039..0.047 rows=1 loops=1)
                     Index Cond: ("outer".supplierid = s.supplierid)
 Total runtime: 677.563 ms

If we change content's priceid then we get the same plan but faster results:

UPDATE content SET priceid = 1;

 Merge Right Join  (cost=4.05..1054.06 rows=1 width=0) (actual time=0.268..0.303 rows=1 loops=1)
   Merge Cond: ("outer".priceid = "inner".priceid)
   ->  Index Scan using price_pkey on price p  (cost=0.00..925.00 rows=50000 width=4) (actual time=0.049..0.061 rows=2 loops=1)
   ->  Sort  (cost=4.05..4.05 rows=1 width=4) (actual time=0.187..0.192 rows=1 loops=1)
         Sort Key: c.priceid
         ->  Nested Loop Left Join  (cost=0.00..4.04 rows=1 width=4) (actual time=0.099..0.128 rows=1 loops=1)
               ->  Seq Scan on content c  (cost=0.00..1.01 rows=1 width=8) (actual time=0.025..0.033 rows=1 loops=1)
               ->  Index Scan using supplier_pkey on supplier s  (cost=0.00..3.01 rows=1 width=4) (actual time=0.046..0.053 rows=1 loops=1)
                     Index Cond: ("outer".supplierid = s.supplierid)
 Total runtime: 0.703 ms

-- 
Michael Fuhr

In response to

Responses

pgsql-performance by date

Next:From: Jean-Pierre PelletierDate: 2005-09-22 23:07:41
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Previous:From: Jean-Pierre PelletierDate: 2005-09-22 22:43:31
Subject: Fw: Queries 15 times slower on 8.1 beta 2 than on 8.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group