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

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

From: "Tomeh, Husam" <htomeh(at)firstam(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Date: 2005-09-22 22:37:14
Message-ID: C45835824D00A844BBD0F032D5CDED920116DD51@pisgana01sxch01.ana.firstamdata.com (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Jean-Pierre
Pelletier
Sent: Thursday, September 22, 2005 3:28 PM
To: John Arbash Meinel
Cc: pgsql-performance(at)postgresql(dot)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(at)arbash-meinel(dot)com>
To: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>
Cc: <pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)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
**********************************************************************


pgsql-performance by date

Next: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
Previous:From: Jean-Pierre PelletierDate: 2005-09-22 22:28:29
Subject: Re: 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