Hmm, I'm really a beginner at this... It turns out that the pg_statistic table in my good database has records in it for the tables in the query, while the pg_statistic table in my bad database has no records for those tables at all! So I guess I need to figure out why pg_autovacuum isn't analyzing those tables. - DAP >-----Original Message----- >From: David Parker >Sent: Wednesday, November 17, 2004 9:44 AM >To: 'Jeff' >Cc: Russell Smith; pgsql-performance@xxxxxxxxxxxxxx >Subject: RE: [PERFORM] query plan question > >I've got pg_autovacuum running on both platforms. I've >verified that the tables involved in the query have the same >number of rows on both databases. > >I'm not sure where to look to see how the stats might be >different. The "good" database's pg_statistic table has 24 >more rows than that in the "bad" database, so there's >definitely a difference. The good database's pg_statistic has >rows for 2 extra tables, but they are not tables involved in >the query in question... > >So something must be up with stats, but can you tell me what >the most signicant columns in the pg_statistic table are for >the planner making its decision? I'm sure this has been >discussed before, so if there's a thread you can point me to, >that would be great - I realize it's a big general question. > >Thanks for your time. > >- DAP > >>-----Original Message----- >>From: Jeff [mailto:threshar@xxxxxxxxxxxxx] >>Sent: Wednesday, November 17, 2004 9:01 AM >>To: David Parker >>Cc: Russell Smith; pgsql-performance@xxxxxxxxxxxxxx >>Subject: Re: [PERFORM] query plan question >> >> >>On Nov 17, 2004, at 7:32 AM, David Parker wrote: >> >>> Oh, I didn't realize that analyze gave that much more info. >>I've got a >>> lot to learn about this tuning stuff ;-) >>> >>> I've attached the output. I see from the new output where the slow >>> query is taking its time (the nested loop at line 10), but I still >>> have no idea why this plan is getting chosen.... >>> >> >>looks like your stats are incorrect on the sparc. >>Did you forget to run vacuum analyze on it? >> >>also, do both db's have the same data loaded? >>there are some very different numbers in terms of actual rows >floating >>around there... >> >>-- >>Jeff Trout <jeff@xxxxxxxxxxxxx> >>http://www.jefftrout.com/ >>http://www.stuarthamm.net/ >> >>
