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

Poor Performance on Postgres 8.0

From: Pallav Kalva <pkalva(at)deg(dot)cc>
To: PERFORM <pgsql-performance(at)postgresql(dot)org>
Subject: Poor Performance on Postgres 8.0
Date: 2005-01-28 15:15:50
Message-ID: 41FA5726.7040502@deg.cc (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Folks ,

     I  am running this query on postgres 8 beta version and it is not 
using the right index, where as if i run the same query on postgres 7.4 
version it uses the right  index .  Here are the explain analyze output 
for both the versions.    can anyone explain this ?


tks.



tables:  attribute table has 200k records, string table has 190 records

\d common.attribute
                                       Table "common.attribute"
     Column     |            Type             |                       
Modifiers
----------------+-----------------------------+-------------------------------------------------------
 attributeid    | integer                     | not null default 
nextval('COMMON.ATTRIBUTESEQ'::text)
 fknamestringid | integer                     | not null
 stringvalue    | text                        |
 integervalue   | integer                     |
 numericvalue   | numeric(14,2)               |
 datevalue      | timestamp without time zone |
 booleanvalue   | boolean                     |
 bigstringvalue | text                        |
Indexes:
    "pk_attribute_attributeid" primary key, btree (attributeid)
    "uk_attribute_fkstringid_stringvalue_integervalue_numericvalue_d" 
unique, btree (fknamestringid, stringvalue, integervalue, numericvalue, 
datevalue)
    "idx_attribute_fknamestringid" btree (fknamestringid)
Foreign-key constraints:
    "fk_attribute_string" FOREIGN KEY (fknamestringid) REFERENCES 
common.string(stringid)



\d common.string
                          Table "common.string"
  Column  |  Type   |                     Modifiers
----------+---------+----------------------------------------------------
 stringid | integer | not null default nextval('COMMON.STRINGSEQ'::text)
 value    | text    |
Indexes:
    "pk_string_stringid" primary key, btree (stringid)


Query

select attribute0_.attributeid as attribut1_, attribute0_.stringvalue as 
stringva2_,
    attribute0_.bigStringvalue as bigStrin3_, attribute0_.integervalue 
as integerv4_,
    attribute0_.numericvalue as numericv5_, attribute0_.datevalue as 
datevalue,
    attribute0_.booleanvalue as booleanv7_, attribute0_.fknamestringid 
as fknamest8_
from  common.attribute attribute0_, common.string text1_
where     (text1_.value='squareFeet' and     
attribute0_.fknamestringid=text1_.stringid)
and     (numericValue='775.0')


Explain Analyze from 7.4

                                                                           
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..501.96 rows=1 width=100) (actual 
time=127.420..135.914 rows=1 loops=1)
   ->  Seq Scan on string text1_  (cost=0.00..12.31 rows=2 width=4) 
(actual time=68.421..68.466 rows=1 loops=1)
         Filter: (value = 'squareFeet'::text)
   ->  Index Scan using idx_attribute_fknamestringid on attribute 
attribute0_  (cost=0.00..244.81 rows=1 width=100) (actual 
time=58.963..67.406 rows=1 loops=1)
         Index Cond: (attribute0_.fknamestringid = "outer".stringid)
         Filter: (numericvalue = 775.0)
 Total runtime: 136.056 ms

Explain Analyze from 8 beta

                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..5440.85 rows=1 width=109) (actual 
time=27.313..440.469 rows=1 loops=1)
   ->  Seq Scan on attribute attribute0_  (cost=0.00..5437.82 rows=1 
width=109) (actual time=26.987..440.053 rows=2 loops=1)
         Filter: (numericvalue = 775.0)
   ->  Index Scan using pk_string_stringid on string text1_  
(cost=0.00..3.02 rows=1 width=4) (actual time=0.169..0.172 rows=0 loops=2)
         Index Cond: ("outer".fknamestringid = text1_.stringid)
         Filter: (value = 'squareFeet'::text)
 Total runtime: 440.648 ms



Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2005-01-28 15:29:58
Subject: Re: PostgreSQL clustering VS MySQL clustering
Previous:From: Alexandre LeclercDate: 2005-01-28 14:25:55
Subject: Re: Flattening a kind of 'dynamic' table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group