Re: [SQL] selecting from indexes

> "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?

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

select id from books, book_words where book_words.word='happy' and
book_words.id = books.id and books.category_key=1471;

this query works fine if the books table (with 1,200,000 rows) is clustered
on category_key, but trundles on for ages (mainly accessing the disk) if
not.

>
>
> > 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.

ok, and because i have 294072 potentially interesting tuples, it hits the
disk hard, and takes forever.

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?  this
would mean about 1000 interesting book tuples which can then be checked for
good categories.  is there a way to do this?

thanks very much for your help.

timj

>
> regards, tom lane