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 12:23:57 -0500
Message-ID: <2858.943032237@sss.pgh.pa.us> (raw)
In-Reply-To: <008001bf32aa$c3fba020$0501a8c0@noonoo>
"Tim Joyce" <tim@hoop.co.uk> writes:
>>>> 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)
> the query above selects 294072 rows, which i obviously don't want to do, but
> I do want to use the clause above in a query that involves a join. eg
Ah, I begin to understand. With an index scan you're going to get
294072 probes into the table (maybe even more, if there are deleted rows
that match the category_key). If the rows are scattered all over the
disk then that may actually take about 300k disk reads. After you
cluster the table, the rows with the same category_key are all
contiguous in the table, so many fewer blocks have to be read to visit
them all. That's why clustering helps here.
Since you're selecting about 1/4th of the table, this particular query
would probably be better off *not* using the index, but just doing a
sequential scan of the whole table :-(. I assume most of your
categories are more selective than this one, though, so dropping the
category index entirely is probably not the answer.
> select id from books, book_words where book_words.word='happy' and
> book_words.id = books.id and books.category_key=1471;
If this is what you're really doing, I think what you actually want is
indexes on book_words.word and books.id. That would allow book_words
to be searched on the word (hopefully giving a more selective result
than the category does), and then books would be probed using the id index.
id has unique values, right?
> perhaps (in the above query) there is a way of directing postgres to only
> access the books that are selected by the 'words' part of the query?
You might want to look at contrib/fulltextindex in the distribution for
ideas about indexing words. fulltextindex might be overkill for your
needs, or maybe not, but you could probably adapt it for your purposes.
regards, tom lane
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: <2858.943032237@sss.pgh.pa.us>
References: <008001bf32aa$c3fba020$0501a8c0@noonoo> <2858.943032237@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