FW: Query Plan problem - Mailing list pgsql-performance

> Hi All,
>
> We have a performance problem with a specific query, where just getting
> the QUERY PLAN (_not_ getting the results of the query itself) on this
> query is taking up to 10 seconds, and spinning the CPU, and basically
> blocking other access to the db.  This same query on a dump/restore on
> another server (different kernel, same postgres version, much less
> powerful box) generates practically the same query plan, but the
> generation of the query plan takes orders of magnitude less (almost
> immmediately generates it).
>
> We noticed this significant performance loss when we upgraded from
> Postgres 7.2.1 -> 7.2.3 over the weekend, we also upgraded our RedHat
> kernel from "2.4.18-4smp i686" to "2.4.20-13.7smp  i686", plus upgraded to
> latest glibc at the same time (probably shouldn't have mixed all those
> upgrades, but there you go).
>
> We did not do a dump restore as part of the postgres/kernal upgrade on our
> production box (docs say upgrade is fine without it).  We vacuumed every
> which way possible.  Several times.  Vacuum full analyze. the lot.  We
> dropped the indexes and recreated them.  We used REINDEX.
>
> This is a UNICODE database, and this table does contain some unicode
> character sequences.
>
> The offending explain statement is:
>
> EXPLAIN SELECT tblUser.id, tblUser.first_name, tblUser.last_name,
> tblUser.login, tblUser.comments, tblUser.title_id, tblUser.bh_phone,
> tblUser.ah_phone, tblUser.mobile, tblUser.fax, tblUser.address,
> tblUser.city, tblUser.state_id, tblUser.country_id, tblUser.postcode,
> tblUser.plain_text_email, tblUser.email_freq_id,
> tblUser.email_freq_day_id, tblUser.privilege, tblUser.secure_id,
> tblUser.activeyn, tblUser.login_attempts, tblUser.hashed_password,
> tblUser.last_password_change, tblUser.forwarding_user_id,
> tblUser.role_name FROM tblUser WHERE tblUser.id IN
> ('102','103','104','105','106','107','108','109','110','111','112','113','
> 114','115','116','117','118','119','120','121','122','123','124','125','12
> 6','127','128','129','130','131','132','133','134','135','136','137','138'
> ,'139','140','141','142','143','144','145','146','147','148','149','150','
> 151','152','153','154','155','156','157','158','159','160','161','162','16
> 3','164','165','166','167','168','169','170','171','172','173','174','175'
> ,'176','177','178','179','180','181','182','183','184','185','186','187','
> 188','189','190','191','192','193','194','195','196','197','198','199','20
> 0','201','202','203','204','205','206','207','208','209','210','211','212'
> ,'213','215','216','217','218','219','220','221','222','223','224','225','
> 226','227','228','229','230','231','233','235','236','237','238','239','24
> 0','241','242','243','244','245','246','247','249','250','251','252','253'
> ,'254','255','256','257','258','259','260','261','262','263','264','265','
> 266','267','268','269','270','271','272','273','274','275','276','277','27
> 8','279','280','281','282','283','284','285','286','287','288','289','290'
> ,'291','292','293','294','295','296','297','298','299','300','301','302','
> 303','304','305','306','307','308','309','310','311','312','313','315','31
> 6','317','318','319','320','321','322','323','324','325','326','327','328'
> ,'329','331','333','334','335','336','337','338','339','340','341','342','
> 343','344','345','346','347','348','349','350','351','352','353','354','35
> 5','356','357','358');
>
NOTICE:  QUERY PLAN:

Seq Scan on tbluser  (cost=0.00..670.82 rows=221 width=3726)

> (this is what our App server generates as part of the query, I KNOW the
> IN() is not the most efficient, but it's working fine on a number of other
> machines, the tbluser table is only 1000 rows, and with an index on the id
> column).
>
> My suspicion is that a dump/restore on our production box may fix this
> problem, but I'd rather know some more about this issue.  Can anyone help
> explain this issue?
>
> regards,
>
> _________________________
> Paul Smith
> Lawlex Compliance Solutions
> phone: +61 3 9278 1511
> email: paul.smith@lawlex.com.au
>
>