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@postgresql.org
>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@torgo.978.org]
>>Sent: Wednesday, November 17, 2004 9:01 AM
>>To: David Parker
>>Cc: Russell Smith; pgsql-performance@postgresql.org
>>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@jefftrout.com>
>>http://www.jefftrout.com/
>>http://www.stuarthamm.net/
>>
>>
