From: Aaron Bono <postgresql@aranya.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequence vs. Index Scan
Date: Sat, 5 May 2007 23:13:42 -0500
Message-ID: <bf05e51c0705052113pa83b2fex99c17f23ff9e228b@mail.gmail.com> (raw)
In-Reply-To: <20070506004053.GA22890@phlogiston.dyndns.org>
References: <bf05e51c0705051405p29ee139fn30c9979f3772a1d4@mail.gmail.com>
<20070505213009.GA22573@phlogiston.dyndns.org>
<bf05e51c0705051500p3e3de711na0d76ae1ad463775@mail.gmail.com>
<20070506004053.GA22890@phlogiston.dyndns.org>
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.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, ajs@crankycanuck.ca
Subject: Re: Sequence vs. Index Scan
In-Reply-To: <bf05e51c0705052113pa83b2fex99c17f23ff9e228b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox