[rt-devel] Postgres performance.

[David Wheeler]

On Monday, April 7, 2003, at 02:40  PM, Greg Stark wrote:

> So first of all, do you run vacuum and analyze on your database 
> frequently? If
> not you should run "vacuum full" and then start running both 
> frequently. How
> frequently depends on the amount of updates your database gets, but as 
> often
> as once an hour is not unusual.

I don't have that many tickets in my database (< 50), and what's in 
there was mainly loaded up from a database dump of an RC2 database. I 
run vacuum and analyze nightly, but haven't run vacuum full.

> The big gun to be sure everything is up-to-date for optimizing like 
> this is:
>  vacuum full analyze
> which cleans up everything and gets accurate statistics.

See attached, where I run this and a few other things before re-running 
the explains.

> The only bad guess postgres is making appears to be on a table called
> CachedGroupMembers. Is it possible this table has received lots of 
> updates
> since the last time the database was analyzed?

Nope. As I said, this database doesn't have much in the way of records 
in it. The only difference I can think of is that, because there aren't 
that many records, the statistics won't yet be accurate.

> That said, I don't think that's the main difference between the 
> original query
> and your optimized one. I think a big problem is that postgres isn't 
> using an
> index on groups. It seems to be having trouble discerning the the 
> groups_2.Id
> = Principals_4.id is on both sides of the OR and can thus be pulled 
> out to do
> an index lookup.

Well, the optimizer may decide that it's not worth it to use an index 
when there are so few records and do a table scan, instead. But then I 
don't know why it would be different for Aidan's version of the query.

> I'm confused by why this isn't happening because some testing shows 
> postgres
> is indeed capable of doing this type of rewriting.
>
> Out of curiosity, what happens if you run

I've re-run the queries, including this one. Summary:

Original query:          20704.24 msec
Aidan's Optimized query:   203.89 msec
Your new query:          18799.20 msec

Something's rotten in Denmark.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david at kineticode.com                              ICQ: 15726394
                                                Yahoo!: dew7e
                                                Jabber: Theory at jabber.org
Kineticode. Setting knowledge in motion.[sm]

>  
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rt-pg-output.txt.gz
Type: application/x-gzip
Size: 3093 bytes
Desc: not available
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030408/0ce6945b/rt-pg-output.txt.bin
-------------- next part --------------
>