pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pallav Kalva <pkalva@deg.cc>
To: PERFORM <pgsql-performance@postgresql.org>
Subject: Poor Performance on Postgres 8.0
Date: Fri, 28 Jan 2005 10:15:50 -0500
Message-ID: <41FA5726.7040502@deg.cc> (raw)

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





view thread (11+ messages)  latest in thread

Message-ID: <41FA5726.7040502@deg.cc>
Permalink:  ../41FA5726.7040502@deg.cc/
Also on:    postgresql.org/message-id/41FA5726.7040502@deg.cc

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pkalva@deg.cc
  Subject: Re: Poor Performance on Postgres 8.0
  In-Reply-To: <41FA5726.7040502@deg.cc>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox