Received: from mail.iinet.net.au (symphony-07.iinet.net.au [203.59.3.39]) by postgresql.org (8.11.3/8.11.4) with SMTP id f9N48hP16295 for ; Tue, 23 Oct 2001 00:08:43 -0400 (EDT) (envelope-from chriskl@familyhealth.com.au) Received: (qmail 26455 invoked by uid 666); 23 Oct 2001 04:08:21 -0000 Received: from unknown (HELO houston.familyhealth.com.au) (203.59.231.6) by mail.iinet.net.au with SMTP; 23 Oct 2001 04:08:21 -0000 Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id f9N47dX07829 for pgsql-sql@postgresql.org; Tue, 23 Oct 2001 12:07:39 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id f9N47ac07732; Tue, 23 Oct 2001 12:07:36 +0800 (WST) From: "Christopher Kings-Lynne" To: "Reiner Dassing" , Cc: Subject: Re: [HACKERS] Index of a table is not used (in any case) Date: Tue, 23 Oct 2001 12:08:14 +0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal In-Reply-To: <3BD3BFE0.55FC3EC0@wettzell.ifag.de> X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Archive-Number: 200110/314 X-Sequence-Number: 5267 > Hello PostgreSQl Users! > > PostSQL V 7.1.1: You should upgrade to 7.1.3 at some point... > I have defined a table and the necessary indices. > But the index is not used in every SELECT. (Therefore, the selects are > *very* slow, due to seq scan on > 20 million entries, which is a test setup up to now) > > The definitions can be seen in the annex. > > Does some body know the reason and how to circumvent the seq scan? Yes. You probably have not run 'VACUUM ANALYZE' on your large table. > Is the order of index creation relevant? I.e., should I create the > indices before inserting > entries or the other way around? If you are inserting a great many entries, insert the data first and then create the indices - it will be much faster this way. > Should a hashing index be used? (I tried this, but I got the known error > "Out of overflow pages") Just do the default CREATE INDEX - btree should be fine... (probably) > The table entry 'epoche' is used in two different indices. Should that > be avoided? It's not a problem, but just check your EXPLAIN output after the VACUUM to check that you have them right. Chris