X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A5212488412 for ; Tue, 4 Mar 2003 23:45:49 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 72029-02 for ; Tue, 4 Mar 2003 23:45:41 -0500 (EST) Received: from smtp.web.de (smtp03.web.de [217.72.192.158]) by postgresql.org (Postfix) with ESMTP id 0CCE34C9DF5 for ; Tue, 4 Mar 2003 16:45:30 -0500 (EST) Received: from p508187e9.dip0.t-ipconnect.de ([80.129.135.233] helo=web.de) by smtp.web.de with asmtp (TLSv1:RC4-MD5:128) (WEB.DE(Exim) 4.97 #53) id 18qKE6-0004DD-00 for pgsql-performance@postgresql.org; Tue, 04 Mar 2003 22:45:31 +0100 Message-ID: <3E651E80.9070807@web.de> Date: Tue, 04 Mar 2003 22:45:36 +0100 From: Andreas Pflug User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Slow query performance on large table References: <000001c2e25c$ad506170$0c64a8c0@paulspc> <23081.1046790781@sss.pgh.pa.us> <3E64D694.9050309@web.de> <3E64E079.8050807@klaster.net> In-Reply-To: <3E64E079.8050807@klaster.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/19 X-Sequence-Number: 1317 Tomasz Myrta wrote: > You are right - primary key should be ok, but Paul lost it. psql \d > shows primary key indexes, but in this case there was no such primary > key. > > Regards, > Tomasz Myrta > Ok, then my view of the world is all right again. Re Tom Lane > One would like to think the optimizer will make the right choice. But > using a two-column index just because it's there isn't necessarily the > right choice. The two-column index will certainly be bulkier and more > expensive to scan, so if there's a one-column index that's nearly as > selective, it might be a better choice. If I know that the access pattern of my app looks as if it will need a multipart index I should create it. If the optimizer finds out, a simpler one will fit better, all right, it knows better (if properly VACUUMed :-). But it's still good practice to offer complete indices. Will pgsql use a multipart index as efficiently for simpler queries as a shorter one covering only the first columns? In this example, the (assessment, time) index could replace the (accessment) index, but certainly not the (time) index. I tend to design longer indices with hopefully valuable columns. In this context: From MSSQL, I know "covering indices". Imagine a table t with many columns, and an index on (a,b,c). in MSSQL, SELECT c from t where (a ... AND b...) will use that index to retrieve the c column value also without touching the row data. In a sense, the index is used as an alternative table. Does pgsql profit from this kind of indices also? Regards, Andreas