Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA17387 for ; Fri, 19 Nov 1999 10:23:24 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA02442; Fri, 19 Nov 1999 10:21:42 -0500 (EST) To: "Tim Joyce" cc: "bloomsbury development" , pgsql-sql@postgreSQL.org Subject: Re: [SQL] selecting from indexes In-reply-to: Your message of Fri, 19 Nov 1999 14:22:24 -0000 <003a01bf329d$2db2f3a0$0501a8c0@noonoo> Date: Fri, 19 Nov 1999 10:21:42 -0500 Message-ID: <2440.943024902@sss.pgh.pa.us> From: Tom Lane "Tim Joyce" writes: > I am trying to improve search times on a moderately large table (approx 1 > GB). > eg, my query is: > SELECT id FROM books WHERE category_key = 1471; > (this takes ages on a table not ordered by category_key even if I have an > index on category_key) This should *not* take a long time if you have an index on category_key. What does EXPLAIN show as the query plan? (I am wondering if maybe the planner doesn't know the table is large, which it wouldn't if you've never vacuumed it... in that case it might be picking a sequential scan instead of using the index.) Also, how many rows are actually selected by the above? > If I created an index: > CREATE INDEX books_category_id ON books(category_key,id); > and then run the above query, it has no need to go to the books table to > retrieve the id, Yes it does, because the index is only a hint. The executor must still fetch each tuple fingered by the index in order to find out whether the tuples are valid (committed). But that fetching should cost at most one disk read per potentially-interesting tuple. Adding id to the index as you show above would be counterproductive, at least for this query. It'd just inflate the size of the index and thus require more I/O to scan the index. A 2-column index is only useful for queries where WHERE constrains both columns. > Another option would be to do the clustering using a view, but: A view doesn't provide any performance advantage, it's only a rule for rewriting your query before it's executed. regards, tom lane