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 20:06:34 -0000
Message-ID: <000201bf32d3$28805310$0501a8c0@noonoo> (raw)
References: <2858.943032237@sss.pgh.pa.us>

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

Indeed, and this lead me to see if there was a way of getting the ids
without hitting the books table, but it doesn't look like there is :(

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

I have indexes on both of these.

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

yep, but how can i use this subset to then select for category?  perhaps at
this stage, I should start to do things in the application code?

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

we have adapted this already (taking out the reg expression stuff so that it
is a bit quicker)

thanks for your help

timj

>
> regards, tom lane




view thread (7+ messages)  latest in thread

Message-ID: <000201bf32d3$28805310$0501a8c0@noonoo>
Permalink:  ../000201bf32d3$28805310$0501a8c0@noonoo/
Also on:    postgresql.org/message-id/000201bf32d3$28805310$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: <000201bf32d3$28805310$0501a8c0@noonoo>
  References: <2858.943032237@sss.pgh.pa.us> <000201bf32d3$28805310$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