Re: Sequence vs. Index Scan

On 5/5/07, Andrew Sullivan <ajs(at)crankycanuck(dot)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
==================================================================