pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tim Joyce <tim@hoop.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: bloomsbury development <bloomsbury_development@messageboards.paneris.org>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] selecting from indexes
Date: Fri, 19 Nov 1999 16:25:40 -0000
Message-ID: <008001bf32aa$c3fba020$0501a8c0@noonoo> (raw)
References: <2440.943024902@sss.pgh.pa.us>

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





view thread (7+ messages)  latest in thread

Message-ID: <008001bf32aa$c3fba020$0501a8c0@noonoo>
Permalink:  ../008001bf32aa$c3fba020$0501a8c0@noonoo/
Also on:    postgresql.org/message-id/008001bf32aa$c3fba020$0501a8c0@noonoo

 · 

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: tim@hoop.co.uk, tgl@sss.pgh.pa.us, bloomsbury_development@messageboards.paneris.org
  Subject: Re: [SQL] selecting from indexes
  In-Reply-To: <008001bf32aa$c3fba020$0501a8c0@noonoo>
  References: <2440.943024902@sss.pgh.pa.us> <008001bf32aa$c3fba020$0501a8c0@noonoo>

* 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