From: Nick Fankhauser <nickf@ontko.com>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql-Performance@Postgresql. Org <pgsql-performance@postgresql.org>
Subject: Re: Seeking help with a query that takes too long
Date: Fri, 14 Nov 2003 11:00:38 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGKEECJHAA.nickf@ontko.com> (raw)
In-Reply-To: <p7a5rv0too2u2p745170r3utnoae5ro28j@email.aon.at>
> Does actor_case_assignment contain more columns than just the two ids?
> If yes, do these additional fields account for ca. 70 bytes per tuple?
> If not, try
> VACUUM FULL ANALYSE actor_case_assignment;
actor_case_assignment has its own primary key and a "role" field in addition
to the ids you've seen, so 70 bytes sounds reasonable. (The PK is to allow a
remote mirroring application to update these records- otherwise it would be
unnecessary.)
> 7ms per
> tuple returned looks like a lot of disk seeks are involved. Is
> clustering actor on actor_full_name_uppercase an option or would this
> slow down other queries?
Good question... I've never used clustering in PostgreSQL before, so I'm
unsure. I presume this is like clustering in Oracle where the table is
ordered to match the index? If so, I think you may be onto something because
the only other field We regularly query on is the actor_id. Actor_id has a
unique index with no clustering currently, so I don't think I'd lose a thing
by clustering on actor_full_name_uppercase.
I'll give this a try & let you know how it changes.
BTW, you are correct that caching has a big affect on the actual time
figures in this case- I'm working on my development DB, so cahced info
doesn't get trampled as quickly by other users. Is there a way to flush out
the cache in a testing situation like this in order to start from a
consistent base?
Thanks!
-Nick
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-performance@postgresql.org
Cc: nickf@ontko.com, mkoi-pg@aon.at, tgl@sss.pgh.pa.us
Subject: Re: Seeking help with a query that takes too long
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGKEECJHAA.nickf@ontko.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