Postgres 7.4 , select with in clausule slow - Mailing list pgsql-general

Hy,

We are developing with php and postgres and a few days ago we tried to
update our
postgres 7.3 to 7.4.

We have one critical problem of performance with 7.4 version. We usually use
a select with the 'in' option in the following way:

select ..... where field in (value1,value2,value3).

With postgres 7.3 it runs fast for us (maybe can be faster with a more
sophisticated query),
but it's sufficient for us.

With postgres 7.4 it's is very slow.... I will explain



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 > select distinct id_entity_fk from locationcache where book=true;

 > select t1.id_entity_fk,t1.id_location,t1.redirect,t1.priority from
locationcache as t1
inner join entityname as t2 on (t1.id_entity_fk = t2.id_entity_fk) where
t1.id_entity_fk in (..........) order by t1.priority,
lower(translate(t2.descr,'áàâäãåÁÀÂÄÃÅéèêëÉÈÊËíìîïÍÌÎÏóòôöõÓÒÔÖÕúùûüÚÙÛÜñÑçÇÿŸ',
'aaaaaaAAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyY'))


Sort (cost=9588794.42..9608441.04 rows=7858649 width=413) (actual
time=14342.904..14343.986 rows=1871 loops=1)
Sort Key: t1.priority, lower(translate((t2.descr)::text,
'áàâäãåÁÀÂÄÃÅéèêëÉÈÊËíìîïÍÌÎÏóòôöõÓÒÔÖÕúùûüÚÙÛÜñÑçÇÿŸ'::text,
'aaaaaaAAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyY'::text))
-> Merge Join (cost=96716.61..254829.25 rows=7858649 width=413) (actual
time=14156.315..14313.133 rows=1871 loops=1)
Merge Cond: ("outer".id_entity_fk = "inner".id_entity_fk)
-> Index Scan using idx_entityname1 on entityname t2 (cost=0.00..886.58
rows=21235 width=404) (actual time=0.150..71.511 rows=21237 loops=1)
-> Sort (cost=96716.61..96901.65 rows=74016 width=13) (actual
time=14149.377..14150.594 rows=1871 loops=1)
Sort Key: t1.id_entity_fk
-> Seq Scan on locationcache t1 (cost=0.00..89958.92 rows=74016
width=13) (actual time=0.651..14144.982 rows=1859 loops=1)
Filter: ((id_entity_fk = 10000636) OR (id_entity_fk = 10001514) OR.......))

Total runtime: 14348.215 ms
(10 rows)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 > select t1.id_entity_fk,t1.id_location,t1.redirect,t1.priority from
locationcache as t1 i
inner join entityname as t2 on (t1.id_entity_fk = t2.id_entity_fk)
where t1.id_entity_fk in (select distinct id_entity_fk from
locationcache where book=true) order by t1.priority,
lower(translate(t2.descr,'áàâäãåÁÀÂÄÃÅéèêëÉÈÊËíìîïÍÌÎÏóòôöõÓÒÔÖÕúùûüÚÙÛÜñÑçÇÿŸ',
'aaaaaaAAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyY'))

Sort (cost=5392676.30..5403862.37 rows=4474427 width=413) (actual
time=651.925..653.016 rows=1871 loops=1)
Sort Key: t1.priority, lower(translate((t2.descr)::text,
'áàâäãåÁÀÂÄÃÅéèêëÉÈÊËíìîïÍÌÎÏóòôöõÓÒÔÖÕúùûüÚÙÛÜñÑçÇÿŸ'::text,
'aaaaaaAAAAAAeeeeEEEEiiiiIIIIoooooOOOOOuuuuUUUUnNcCyY'::text))
-> Merge Join (cost=9454.44..99882.65 rows=4474427 width=413) (actual
time=465.026..622.489 rows=1871 loops=1)
Merge Cond: ("outer".id_entity_fk = "inner".id_entity_fk)
-> Index Scan using idx_entityname1 on entityname t2 (cost=0.00..886.58
rows=21235 width=404) (actual time=0.087..71.750 rows=21237 loops=1)
-> Sort (cost=9454.44..9559.80 rows=42142 width=17) (actual
time=457.800..459.090 rows=1871 loops=1)
Sort Key: t1.id_entity_fk
-> Hash Join (cost=3911.66..6217.31 rows=42142 width=17) (actual
time=240.469..453.780 rows=1859 loops=1)
Hash Cond: ("outer".id_entity_fk = "inner".id_entity_fk)
-> Seq Scan on locationcache t1 (cost=0.00..1462.82 rows=84282 width=13)
(actual time=0.011..116.880 rows=84447 loops=1)
-> Hash (cost=3911.41..3911.41 rows=100 width=4) (actual
time=240.392..240.392 rows=0 loops=1)
-> HashAggregate (cost=3911.41..3911.41 rows=100 width=4) (actual
time=239.218..239.841 rows=420 loops=1)
-> Subquery Scan "IN_subquery" (cost=0.00..3911.16 rows=100 width=4)
(actual time=0.239..237.964 rows=420 loops=1)
-> Unique (cost=0.00..3910.16 rows=100 width=4) (actual
time=0.234..236.977 rows=420 loops=1)
-> Index Scan using i_locationcache1 on locationcache
(cost=0.00..3804.81 rows=42141 width=4) (actual time=0.230..234.555
rows=1859 loops=1)
Filter: (book = true)
Total runtime: 656.692 ms
(17 rows)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Can any one help me, please.
I don't know what's the problem, but I'm sure that it can't be a problem
of the new version ( postgres => Good Software).... I start to think that
it could be a problem of the compilation, system tuning or postgres
configuration.... I'm really lost !!! :-(

Thanks

Gabi Ramos