On 5/5/07,
Andrew Sullivan <
ajs@crankycanuck.ca> wrote:
On Sat, May 05, 2007 at 05:00:53PM -0500, Aaron Bono wrote:
> We don't allow deletes and updates are fairly infrequent. I also did a
> vacuum analyze to no effect.
How do you "not allow" deletes?
Permissions are set on the tables to only allow the application to do select/insert/update. We use start/end dates to indicate that records are no longer active and then have the application run against views that filter out inactive records. It also allows "removal" of records sometime in the future, gives us the ability to "undelete" records, and keeps us from having to code the application to have to clean up because of foreign key constraints. There are other advantages.
I'd look at
the output of VACUUM VERBOSE to make sure you don't have a lot of
dead tuples.
Fast Schema:
psql:vacuumverbose.sql:1: INFO: vacuuming "fast_schema.branch"
psql:vacuumverbose.sql:1: INFO: index "branch_pkey" now contains 150 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
Slow Schema:
psql:vacuumverbose.sql:1: INFO: vacuuming "slow_schema.branch"
psql:vacuumverbose.sql:1: INFO: index "branch_pkey" now contains 29 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
Like Tom said earlier, I really think it has something to do with the volatile/stable indicator on the function but I changed that. The function in the slow schema acts like it is still volatile while the fast schema is obviously stable.
Is there another way to analyze how the database is using the function in the selects? That or is there a way to just look at how the function is being used by the optimizer?
I do appreciate the feedback.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==================================================================