pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jean-Pierre Pelletier <pelletier_32@sympatico.ca>
To: Michael Fuhr <mike@fuhr.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Cc: John Arbash Meinel <john@arbash-meinel.com>
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Date: Thu, 22 Sep 2005 19:07:41 -0400
Message-ID: <BAYC1-PASMTP04E49D978E6756650A97F695970@CEZ.ICE> (raw )
References: <BAYC1-PASMTP01EE67AD3A0034394D85D895970@CEZ.ICE >
<433326A6.8010708@arbash-meinel.com >
<BAYC1-PASMTP0276B50280C40ABC9BBCB495970@CEZ.ICE >
<200509221519.05555.josh@agliodbs.com >
<20050922225432.GA25103@winnie.fuhr.org >
I don't know if it makes a difference but in my tables,
content.supplierid and content.priceid were nullable.
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: <pgsql-performance@postgresql.org>; "Jean-Pierre Pelletier"
<pelletier_32@sympatico.ca >; "John Arbash Meinel" <john@arbash-meinel.com>
Sent: Thursday, September 22, 2005 6:54 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
> 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
view thread (21+ messages) latest in thread
Message-ID: <BAYC1-PASMTP04E49D978E6756650A97F695970@CEZ.ICE>
Permalink: ../BAYC1-PASMTP04E49D978E6756650A97F695970@CEZ.ICE/
Also on: postgresql.org/message-id/BAYC1-PASMTP04E49D978E6756650A97F695970@CEZ.ICE
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pelletier_32@sympatico.ca, mike@fuhr.org, josh@agliodbs.com, john@arbash-meinel.com
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
In-Reply-To: <BAYC1-PASMTP04E49D978E6756650A97F695970@CEZ.ICE>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox