Re: Queries 15 times slower on 8.1 beta 2 than on 8.0 - Mailing list pgsql-performance

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
[mailto: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
**********************************************************************