Re: Database tuning - Mailing list pgsql-general

Hello,

The problem is not the database, but your query. You are asking your
poor Pentium II to sort "your" Bible in a random order!! And then you
will only retrieve the first entry.

If you actually want to get a uniformly chosen random verse from The
Bible, and you actually CARE about the quality of your randomness you
can use your id field. Do I understand well if I read "each verse has an
unique ID" ?

You can index that table by your id, and then SELECT ... WHERE
bible.book = books.id AND bible.id=XXX;

XXX is a random value ranking from 0 (or 1) to the maximum id value. You
generate it outside the database.

You can also try (but I am not certain at all that these may work):

SELECT ... WHERE bible.book = books.id AND bible.id=(MAX*random());

But you still need to know MAX.


Both approaches need the certainty that every different ID has an
associated verse.

You can even store the max on some other table, if you do not want to
care about it in your application, and then issue:

SELECT ... WHERE bible.book = books.id AND bible.id=(select
maxval*random() from counttable);

I hope that helps.

Anyway, before trying any query on a big database, an EXPLAIN should be
helpful/meaningful.

If you see SEQ SCAN on a very long table, that is certainly BAD.

Standard question: Did you VACUUM ANALYZE? If not, do it.

Good luck!

Antonio


K Old wrote:

> Hello all,
>
> I have a PostgreSQL database that is storing The Bible.  It has 31,103
> records in it and I have a PHP page executing this query:
> SELECT
> books.book_name, bible.chapter, bible.verse, bible.versetext
> FROM asv_bible bible, book_bible books WHERE bible.book = books.id
> ORDER BY random() LIMIT 1
>
> The database schema is:
>
> /* --------------------------------------------------------
>  Sequences
> -------------------------------------------------------- */
> CREATE SEQUENCE "book_bible_seq" start 1 increment 1 maxvalue
> 2147483647 minvalue 1 cache 1;
>
> /* --------------------------------------------------------
>  Table structure for table "asv_bible"
> -------------------------------------------------------- */
> CREATE TABLE "asv_bible" (
>   "id" int8 NOT NULL,
>   "book" int8,
>   "chapter" int8,
>   "verse" int8,
>   "versetext" text,
>   CONSTRAINT "asv_bible_pkey" PRIMARY KEY ("id")
> );
>
>
>
> /* --------------------------------------------------------
>  Table structure for table "book_bible"
> -------------------------------------------------------- */
> CREATE TABLE "book_bible" (
>   "id" int4 DEFAULT nextval('book_bible_seq'::text) NOT NULL,
>   "book_name" varchar(20),
>   CONSTRAINT "book_bible_pkey" PRIMARY KEY ("id")
> );
>
> Right now it takes 9 seconds to return the results.  I don't think
> that it has anything to do with the language executing it, as I have
> run  the same query in via Perl and had the same luck.
>
> I was wondering if anyone could offer any help with lowering the time
> it takes to run?
>
> Am I optimizing the database correctly?
>
> My hardware is a Pentium II 400 with 128MB of RAM.
>
> Or if this is the normal runtime for a database of this size, I'd just
> like confirmation.
>
> Thanks,
> Kevin
>
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> .
>