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.


-----Original Message-----
[mailto:[EMAIL PROTECTED] On Behalf Of Jean-Pierre
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 |
    "price_pkey" PRIMARY KEY, btree (priceid)

                                       Table "public.supplier"
       Column        |              Type              | 
 supplierid          | integer                        | not null default

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


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

 Merge Left Join  (cost=100000005.60..101607964.74 rows=1 width=0)
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 
..0.067 rows=1 loops=1)
         Sort Key: c.priceid
         ->  Nested Loop Left Join  (cost=100000000.00..100000005.59
h=4) (actual time=0.038..0.049 rows=1 loops=1)
               ->  Seq Scan on content c
ws=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)
               ->  Index Scan using "Supplier Id" on supplier s 
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 
5 width=4) (actual time=0.008..370.854 rows=164842 loops=1)
 Total runtime: 729.192 ms

----- Original Message ----- 
From: "John Arbash Meinel" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED] 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to