X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5A3FBD1B4E4 for ; Fri, 14 Nov 2003 16:01:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 10469-04 for ; Fri, 14 Nov 2003 12:01:15 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 1AF4BD1B50C for ; Fri, 14 Nov 2003 12:01:15 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id hAEG0n5f024840; Fri, 14 Nov 2003 11:00:50 -0500 Reply-To: From: "Nick Fankhauser" To: "Manfred Koizar" Cc: "Tom Lane" , "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: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal In-Reply-To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/111 X-Sequence-Number: 4671 > 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