Hi,

I've got many queries running much slower on 8.1 beta2 than on 8.0.1
Here is a simplified one that takes 484 ms on 8.1 and 32 ms on 8.0.1.

select

from
Content C

left outer join Supplier S
on C.SupplierId = S.SupplierId

left outer join Price P
on C.PriceId = P.PriceId;

Any ideas why it's slower?

Thanks
Jean-Pierre Pelletier
e-djuster

======================================================

create table Price (
PriceId INTEGER NOT NULL DEFAULT NEXTVAL('PriceId'),
ItemId INTEGER NOT NULL,
SupplierId INTEGER NOT NULL,
LocationId SMALLINT NULL,
FromDate DATE NOT NULL DEFAULT CURRENT_DATE,
UnitValue DECIMAL NOT NULL,
InsertedByPersonId INTEGER NOT NULL,
LastUpdatedByPersonId INTEGER NULL,
InsertTimestamp TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
LastUpdateTimeStamp TIMESTAMP(0) NULL
);

alter table price add primary key (priceid);

create table Supplier (
SupplierId INTEGER NOT NULL DEFAULT NEXTVAL('SupplierId'),
SupplierDescription VARCHAR(50) NOT NULL,
InsertTimestamp TIMESTAMP(0) NULL DEFAULT CURRENT_TIMESTAMP,
ApprovalDate DATE NULL
);

alter table supplier add primary key (supplierid);

-- I've only put one row in table Content because it was sufficient to
produce
-- the slowdown

create table content (contentid integer not null, supplierid integer,
priceid integer);
insert into content VALUES (148325, 12699, 388026);

vacuum analyze content; -- 1 row
vacuum analyze price; -- 581475 rows
vacuum analyze supplier; -- 10139 rows

======================================================
Here are the query plans:

On "PostgreSQL 8.1beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)"

explain 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;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..18591.77 rows=1 width=0)
Join Filter: ("outer".priceid = "inner".priceid)
-> Nested Loop Left Join (cost=0.00..5.59 rows=1 width=4)
-> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8)
-> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.56
rows=1 width=4)
Index Cond: ("outer".supplierid = s.supplierid)
-> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)


"PostgreSQL 8.0.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"

explain 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;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..11.08 rows=1 width=0)
-> Nested Loop Left Join (cost=0.00..5.53 rows=1 width=4)
-> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8)
-> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.51
rows=1 width=4)
Index Cond: ("outer".supplierid = s.supplierid)
-> Index Scan using price_pkey on price p (cost=0.00..5.53 rows=1
width=4)
Index Cond: ("outer".priceid = p.priceid)

Search Discussions

  • Gavin M. Roy at Sep 22, 2005 at 9:32 pm
    What stood out to me the most was:
    On Sep 22, 2005, at 2:20 PM, Jean-Pierre Pelletier wrote:

    -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
    a) is the index there, b) have you analyzed, c) perhaps the planners
    have different default values for when to use an index vrs a
    seqscan... if you turn off seqscan, are the timings similar?

    Gavin M. Roy
    800 Pound Gorilla
    gmr@ehpg.net
  • Jean-Pierre Pelletier at Sep 22, 2005 at 9:38 pm
    All indexes are there, and I've analyzed the three tables.

    I turned off seq scan, the query plans became identical but the performance
    was not better.

    ----- Original Message -----
    From: Gavin M. Roy
    To: Jean-Pierre Pelletier
    Cc: pgsql-performance@postgresql.org
    Sent: Thursday, September 22, 2005 5:32 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0


    What stood out to me the most was:


    On Sep 22, 2005, at 2:20 PM, Jean-Pierre Pelletier wrote:


    -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)



    a) is the index there, b) have you analyzed, c) perhaps the planners have different default values for when to use an index vrs a seqscan... if you turn off seqscan, are the timings similar?


    Gavin M. Roy
    800 Pound Gorilla
    gmr@ehpg.net
  • John Arbash Meinel at Sep 22, 2005 at 9:48 pm

    Jean-Pierre Pelletier wrote:
    Hi,

    I've got many queries running much slower on 8.1 beta2 than on 8.0.1
    Here is a simplified one that takes 484 ms on 8.1 and 32 ms on 8.0.1.

    select

    from
    Content C

    left outer join Supplier S
    on C.SupplierId = S.SupplierId

    left outer join Price P
    on C.PriceId = P.PriceId;

    Any ideas why it's slower?
    You really have to post the results of "EXPLAIN ANALYZE" not just
    explain. So that we can tell what the planner is expecting, versus what
    really happened.

    John
    =:->
    Thanks
    Jean-Pierre Pelletier
    e-djuster
  • Jean-Pierre Pelletier at Sep 22, 2005 at 9:58 pm
    Here are the explain analyze:

    On 8.1 beta2:

    "Nested Loop Left Join (cost=0.00..18591.77 rows=1 width=0) (actual
    time=1320.302..2439.066 rows=1 loops=1)"
    " Join Filter: ("outer".priceid = "inner".priceid)"
    " -> Nested Loop Left Join (cost=0.00..5.59 rows=1 width=4) (actual
    time=0.044..0.058 rows=1 loops=1)"
    " -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual
    time=0.009..0.011 rows=1 loops=1)"
    " -> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.56
    rows=1 width=4) (actual time=0.016..0.022 rows=1 loops=1)"
    " Index Cond: ("outer".supplierid = s.supplierid)"
    " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
    (actual time=0.004..1143.720 rows=581475 loops=1)"
    "Total runtime: 2439.211 ms"

    On 8.0.1:

    "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual
    time=0.000..0.000 rows=1 loops=1)"
    " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual
    time=0.000..0.000 rows=1 loops=1)"
    " -> Seq Scan on content c (cost=0.00..1.01 rows=1 width=8) (actual
    time=0.000..0.000 rows=1 loops=1)"
    " -> Index Scan using "Supplier Id" on supplier s (cost=0.00..4.46
    rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)"
    " Index Cond: ("outer".supplierid = s.supplierid)"
    " -> Index Scan using "Price Id" on price p (cost=0.00..5.53 rows=1
    width=4) (actual time=0.000..0.000 rows=1 loops=1)"
    " Index Cond: ("outer".priceid = p.priceid)"
    "Total runtime: 0.000 ms"

    ----- Original Message -----
    From: "John Arbash Meinel" <john@arbash-meinel.com>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Cc: <pgsql-performance@postgresql.org>
    Sent: Thursday, September 22, 2005 5:48 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
  • John Arbash Meinel at Sep 22, 2005 at 10:03 pm

    Jean-Pierre Pelletier wrote:
    Here are the explain analyze:
    What is the explain analyze if you use "set enable_seqscan to off"?

    Also, can you post the output of:
    \d supplier
    \d price
    \d content

    Mostly I just want to see what the indexes are, in the case that you
    don't want to show us your schema.

    John
    =:->
  • Jean-Pierre Pelletier at Sep 22, 2005 at 10:28 pm
    With enable-seq-scan = off, it runs in 350 ms so better than 484 ms
    but still much slower than 32 ms in 8.0.1.

    ==============================================

    Table "public.content"
    Column | Type | Modifiers
    ------------+---------+-----------
    contentid | integer | not null
    supplierid | integer |
    priceid | integer |

    Table "public.price"
    Column | Type | Modifiers
    -----------------------+--------------------------------+-----------
    priceid | integer | not null
    itemid | integer |
    supplierid | integer |
    locationid | smallint |
    fromdate | date |
    unitvalue | numeric |
    insertedbypersonid | integer |
    lastupdatedbypersonid | integer |
    inserttimestamp | timestamp(0) without time zone |
    lastupdatetimestamp | timestamp(0) without time zone |
    Indexes:
    "price_pkey" PRIMARY KEY, btree (priceid)

    Table "public.supplier"
    Column | Type |
    Modifie
    rs
    ---------------------+--------------------------------+-------------------------
    ---------------------
    supplierid | integer | not null default
    nextval
    ('SupplierId'::text)
    supplierdescription | character varying(50) | not null
    inserttimestamp | timestamp(0) without time zone | default now()
    approvaldate | date |
    Indexes:
    "Supplier Id" PRIMARY KEY, btree (supplierid)
    "Supplier Description" UNIQUE, btree (upper(supplierdescription::text))
    "Supplier.InsertTimestamp" btree (inserttimestamp)
    Check constraints:
    "Supplier Name cannot be empty" CHECK (btrim(supplierdescription::text)
    <> ''::tex

    ================================================================================


    Explan analyze with enable-seq-scan = off on 8.1 beta2
    QUERY PLAN

    --------------------------------------------------------------------------------
    ------------------------------------------------------------
    Merge Left Join (cost=100000005.60..101607964.74 rows=1 width=0) (actual
    time=
    729.067..729.078 rows=1 loops=1)
    Merge Cond: ("outer".priceid = "inner".priceid)
    -> Sort (cost=100000005.60..100000005.60 rows=1 width=4) (actual
    time=0.064
    ..0.067 rows=1 loops=1)
    Sort Key: c.priceid
    -> Nested Loop Left Join (cost=100000000.00..100000005.59 rows=1
    widt
    h=4) (actual time=0.038..0.049 rows=1 loops=1)
    -> Seq Scan on content c (cost=100000000.00..100000001.01
    ro
    ws=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)
    -> Index Scan using "Supplier Id" on supplier s
    (cost=0.00..4.5
    6 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)
    Index Cond: ("outer".supplierid = s.supplierid)
    -> Index Scan using "Price Id" on price p (cost=0.00..1606505.44
    rows=58147
    5 width=4) (actual time=0.008..370.854 rows=164842 loops=1)
    Total runtime: 729.192 ms

    ----- Original Message -----
    From: "John Arbash Meinel" <john@arbash-meinel.com>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Cc: <pgsql-performance@postgresql.org>
    Sent: Thursday, September 22, 2005 6:03 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
  • Josh Berkus at Sep 22, 2005 at 10:15 pm
    Jean-Pierre,

    First off, you're on Windows?
    " -> 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 ...
    "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual
    time=0.000..0.000 rows=1 loops=1)"
    " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual
    time=0.000..0.000 rows=1 loops=1)"
    "Total runtime: 0.000 ms"
    Feh, this looks like the "windows does not report times" bug, which makes
    it hard to compare ...

    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Michael Fuhr at Sep 22, 2005 at 10:54 pm

    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
  • Jean-Pierre Pelletier at Sep 22, 2005 at 11:07 pm
    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
  • Tom Lane at Sep 22, 2005 at 11:12 pm

    Michael Fuhr writes:
    I've created a simplified, self-contained test case for this:
    I see the problem --- I broke best_inner_indexscan() for some cases
    where the potential indexscan clause is an outer-join ON clause.

    regards, tom lane
  • Michael Fuhr at Sep 22, 2005 at 11:17 pm

    On Thu, Sep 22, 2005 at 07:07:41PM -0400, Jean-Pierre Pelletier wrote:
    I don't know if it makes a difference but in my tables,
    content.supplierid and content.priceid were nullable.
    That makes no difference in the tests I've done.

    Tom Lane says he's found the problem; I expect he'll be committing
    a fix shortly.

    --
    Michael Fuhr
  • Jean-Pierre Pelletier at Sep 22, 2005 at 11:26 pm
    Thanks everybody for your help, I'll be awaiting the fix.

    I've also noticed that pg_stat_activity is always empty even if
    stats_start_collector = on

    ----- Original Message -----
    From: "Michael Fuhr" <mike@fuhr.org>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-performance@postgresql.org>;
    "John Arbash Meinel" <john@arbash-meinel.com>
    Sent: Thursday, September 22, 2005 7:17 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    On Thu, Sep 22, 2005 at 07:07:41PM -0400, Jean-Pierre Pelletier wrote:
    I don't know if it makes a difference but in my tables,
    content.supplierid and content.priceid were nullable.
    That makes no difference in the tests I've done.

    Tom Lane says he's found the problem; I expect he'll be committing
    a fix shortly.

    --
    Michael Fuhr

    ---------------------------(end of broadcast)---------------------------
    TIP 1: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
  • Josh Berkus at Sep 22, 2005 at 11:31 pm
    Jean-Pierre,
    Thanks everybody for your help, I'll be awaiting the fix.

    I've also noticed that pg_stat_activity is always empty even if
    stats_start_collector = on
    Yes, I believe that this is a know Windows issue. Not sure if it's fixed
    in 8.1.

    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Tom Lane at Sep 22, 2005 at 11:50 pm

    Michael Fuhr writes:
    Tom Lane says he's found the problem; I expect he'll be committing
    a fix shortly.
    The attached patch allows it to generate the expected plan, at least
    in the test case I tried.

    regards, tom lane

    *** src/backend/optimizer/path/indxpath.c.orig Sun Aug 28 18:47:20 2005
    --- src/backend/optimizer/path/indxpath.c Thu Sep 22 19:17:41 2005
    ***************
    *** 955,969 ****
    /*
    * Examine each joinclause in the joininfo list to see if it matches any
    * key of any index. If so, add the clause's other rels to the result.
    - * (Note: we consider only actual participants, not extraneous rels
    - * possibly mentioned in required_relids.)
    */
    foreach(l, rel->joininfo)
    {
    RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
    Relids other_rels;

    ! other_rels = bms_difference(joininfo->clause_relids, rel->relids);
    if (matches_any_index(joininfo, rel, other_rels))
    outer_relids = bms_join(outer_relids, other_rels);
    else
    --- 955,967 ----
    /*
    * Examine each joinclause in the joininfo list to see if it matches any
    * key of any index. If so, add the clause's other rels to the result.
    */
    foreach(l, rel->joininfo)
    {
    RestrictInfo *joininfo = (RestrictInfo *) lfirst(l);
    Relids other_rels;

    ! other_rels = bms_difference(joininfo->required_relids, rel->relids);
    if (matches_any_index(joininfo, rel, other_rels))
    outer_relids = bms_join(outer_relids, other_rels);
    else
  • Jean-Pierre Pelletier at Sep 22, 2005 at 11:10 pm
    my settings are:

    effective_cache_size = 1000
    random_page_cost = 4
    work_mem = 20000

    ----- Original Message -----
    From: "Josh Berkus" <josh@agliodbs.com>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Sent: Thursday, September 22, 2005 6:58 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    Jean-Pierre,
    How do I produce an "Index scan plan" ?
    You just did. What's your effective_cache_size set to?
    random_page_cost? work_mem?

    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Josh Berkus at Sep 22, 2005 at 11:12 pm
    Jean-Pierre,
    effective_cache_size = 1000
    Try setting this to 16,384 as a test.
    random_page_cost = 4
    Try setting this to 2.5 as a test.
    work_mem = 20000
    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Jean-Pierre Pelletier at Sep 23, 2005 at 1:41 am
    Explain analyze on my 8.0.1 installation does report the time for
    slower queries but for this small query it reports 0.000 ms

    ----- Original Message -----
    From: "Josh Berkus" <josh@agliodbs.com>
    To: <pgsql-performance@postgresql.org>
    Cc: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>; "John Arbash
    Meinel" <john@arbash-meinel.com>
    Sent: Thursday, September 22, 2005 6:19 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    Jean-Pierre,

    First off, you're on Windows?
    " -> 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 ...
    "Nested Loop Left Join (cost=0.00..11.02 rows=1 width=0) (actual
    time=0.000..0.000 rows=1 loops=1)"
    " -> Nested Loop Left Join (cost=0.00..5.48 rows=1 width=4) (actual
    time=0.000..0.000 rows=1 loops=1)"
    "Total runtime: 0.000 ms"
    Feh, this looks like the "windows does not report times" bug, which makes
    it hard to compare ...

    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco

    ---------------------------(end of broadcast)---------------------------
    TIP 3: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq
  • Tomeh, Husam at Sep 22, 2005 at 10:37 pm
    Have tried adjusting the effective_cache_size so that you don't the
    planner may produce a better explain plan for you and not needing to set
    seqscan to off.


    --
    Husam

    -----Original Message-----
    From: pgsql-performance-owner@postgresql.org
    On Behalf Of Jean-Pierre
    Pelletier
    Sent: Thursday, September 22, 2005 3:28 PM
    To: John Arbash Meinel
    Cc: pgsql-performance@postgresql.org
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    With enable-seq-scan = off, it runs in 350 ms so better than 484 ms
    but still much slower than 32 ms in 8.0.1.

    ==============================================

    Table "public.content"
    Column | Type | Modifiers
    ------------+---------+-----------
    contentid | integer | not null
    supplierid | integer |
    priceid | integer |

    Table "public.price"
    Column | Type | Modifiers
    -----------------------+--------------------------------+-----------
    priceid | integer | not null
    itemid | integer |
    supplierid | integer |
    locationid | smallint |
    fromdate | date |
    unitvalue | numeric |
    insertedbypersonid | integer |
    lastupdatedbypersonid | integer |
    inserttimestamp | timestamp(0) without time zone |
    lastupdatetimestamp | timestamp(0) without time zone |
    Indexes:
    "price_pkey" PRIMARY KEY, btree (priceid)

    Table "public.supplier"
    Column | Type |
    Modifie
    rs
    ---------------------+--------------------------------+-----------------
    --------
    ---------------------
    supplierid | integer | not null default

    nextval
    ('SupplierId'::text)
    supplierdescription | character varying(50) | not null
    inserttimestamp | timestamp(0) without time zone | default now()
    approvaldate | date |
    Indexes:
    "Supplier Id" PRIMARY KEY, btree (supplierid)
    "Supplier Description" UNIQUE, btree
    (upper(supplierdescription::text))
    "Supplier.InsertTimestamp" btree (inserttimestamp)
    Check constraints:
    "Supplier Name cannot be empty" CHECK
    (btrim(supplierdescription::text)
    <> ''::tex

    ========================================================================
    ========


    Explan analyze with enable-seq-scan = off on 8.1 beta2
    QUERY
    PLAN

    ------------------------------------------------------------------------
    --------
    ------------------------------------------------------------
    Merge Left Join (cost=100000005.60..101607964.74 rows=1 width=0)
    (actual
    time=
    729.067..729.078 rows=1 loops=1)
    Merge Cond: ("outer".priceid = "inner".priceid)
    -> Sort (cost=100000005.60..100000005.60 rows=1 width=4) (actual
    time=0.064
    ..0.067 rows=1 loops=1)
    Sort Key: c.priceid
    -> Nested Loop Left Join (cost=100000000.00..100000005.59
    rows=1
    widt
    h=4) (actual time=0.038..0.049 rows=1 loops=1)
    -> Seq Scan on content c
    (cost=100000000.00..100000001.01
    ro
    ws=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)
    -> Index Scan using "Supplier Id" on supplier s
    (cost=0.00..4.5
    6 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)
    Index Cond: ("outer".supplierid = s.supplierid)
    -> Index Scan using "Price Id" on price p (cost=0.00..1606505.44
    rows=58147
    5 width=4) (actual time=0.008..370.854 rows=164842 loops=1)
    Total runtime: 729.192 ms

    ----- Original Message -----
    From: "John Arbash Meinel" <john@arbash-meinel.com>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Cc: <pgsql-performance@postgresql.org>
    Sent: Thursday, September 22, 2005 6:03 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0



    ---------------------------(end of broadcast)---------------------------
    TIP 1: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly

    **********************************************************************
    This message contains confidential information intended only for the
    use of the addressee(s) named above and may contain information that
    is legally privileged. If you are not the addressee, or the person
    responsible for delivering it to the addressee, you are hereby
    notified that reading, disseminating, distributing or copying this
    message is strictly prohibited. If you have received this message by
    mistake, please immediately notify us by replying to the message and
    delete the original message immediately thereafter.

    Thank you. FADLD Tag
    **********************************************************************
  • Tomeh, Husam at Sep 22, 2005 at 11:19 pm
    The recommendation for effective_cache_size is about 2/3 of your
    server's physical RAM (if the server is dedicated only for postgres).
    This should have a significant impact on whether Postgres planner
    chooses indexes over sequential scans.

    --
    Husam


    -----Original Message-----
    From: pgsql-performance-owner@postgresql.org
    On Behalf Of Jean-Pierre
    Pelletier
    Sent: Thursday, September 22, 2005 4:10 PM
    To: josh@agliodbs.com
    Cc: pgsql-performance@postgresql.org
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    my settings are:

    effective_cache_size = 1000
    random_page_cost = 4
    work_mem = 20000

    ----- Original Message -----
    From: "Josh Berkus" <josh@agliodbs.com>
    To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
    Sent: Thursday, September 22, 2005 6:58 PM
    Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0

    Jean-Pierre,
    How do I produce an "Index scan plan" ?
    You just did. What's your effective_cache_size set to?
    random_page_cost? work_mem?

    --
    --Josh

    Josh Berkus
    Aglio Database Solutions
    San Francisco
    ---------------------------(end of broadcast)---------------------------
    TIP 3: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq

    **********************************************************************
    This message contains confidential information intended only for the
    use of the addressee(s) named above and may contain information that
    is legally privileged. If you are not the addressee, or the person
    responsible for delivering it to the addressee, you are hereby
    notified that reading, disseminating, distributing or copying this
    message is strictly prohibited. If you have received this message by
    mistake, please immediately notify us by replying to the message and
    delete the original message immediately thereafter.

    Thank you. FADLD Tag
    **********************************************************************

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedSep 22, '05 at 9:20p
activeSep 23, '05 at 1:41a
posts20
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase