pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Joyce <tim@hoop.co.uk>
Cc: bloomsbury development <bloomsbury_development@messageboards.paneris.org>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] selecting from indexes
Date: Fri, 19 Nov 1999 10:21:42 -0500
Message-ID: <2440.943024902@sss.pgh.pa.us> (raw)
In-Reply-To: <003a01bf329d$2db2f3a0$0501a8c0@noonoo>

"Tim Joyce" <tim@hoop.co.uk> 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



view thread (7+ messages)  latest in thread

Message-ID: <2440.943024902@sss.pgh.pa.us>
Permalink:  ../2440.943024902@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2440.943024902@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, tim@hoop.co.uk, bloomsbury_development@messageboards.paneris.org
  Subject: Re: [SQL] selecting from indexes
  In-Reply-To: <2440.943024902@sss.pgh.pa.us>
  References: <003a01bf329d$2db2f3a0$0501a8c0@noonoo> <2440.943024902@sss.pgh.pa.us>

* 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