Poor Performance on Postgres 8.0 - Mailing list 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