pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <pg@rbt.ca>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: Vitaly Belman <vitalib@012.net.il>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Simply join in PostrgeSQL takes too long
Date: Wed, 28 Apr 2004 08:23:35 -0400
Message-ID: <1083155014.30065.189.camel@jester> (raw)
In-Reply-To: <tlpu80tqhmnpc2sl63boejki6o4vbn1p3q@email.aon.at>
References: <282909734.20040428002740@012.net.il>
	<1083103293.30065.100.camel@jester>
	<tlpu80tqhmnpc2sl63boejki6o4vbn1p3q@email.aon.at>

> Rod, he has a hierarchy of genres.  Genre 1 has 6379 child genres and a
> book can be in more than one of these.

     bookgenres.genre_id = genre_children.genre_child_id AND
     genre_children.genre_id = 1

I see, sorry. I didn't notice the genre_child_id in the where clause.
First glance had them all as genre_id.

When I run into this I usually create a 3rd table managed by triggers
that would relate the book to all genre entries. Insert takes a little
longer, but the selects can still be very quick.

The below plpgsql forces the kind of algorithm we wish the planner could
choose. It should be fairly quick irregardless of dataset.


CREATE OR REPLACE FUNCTION book_results(numeric) RETURNS SETOF numeric
AS
'
DECLARE
  v_genre ALIAS FOR $1;
  v_limit integer = 10;
  t_rows RECORD;
  v_transmitted integer = 0;

  v_transmitted_values numeric[] = ARRAY[1];

BEGIN
  FOR t_rows IN SELECT book_id
                  FROM bv_bookgenres AS b
                  JOIN bv_genre_children AS g ON (b.genre_id =
g.genre_child_id)
                 WHERE g.genre_id = v_genre
                  LOOP

    -- If this is a new value, transmit it to the end user
    IF NOT t_rows.book_id = ANY(v_transmitted_values) THEN
      v_transmitted_values := array_append(v_transmitted_values,
t_rows.book_id);
      v_transmitted := v_transmitted + 1;
      RETURN NEXT t_rows.book_id;
    END IF;

    EXIT WHEN v_transmitted >= v_limit;
  END LOOP;

  RETURN;
END;
' LANGUAGE plpgsql;

EXPLAIN ANALYZE SELECT * FROM book_results(1);
SELECT * FROM book_results(1);




view thread (12+ messages)  latest in thread

Message-ID: <1083155014.30065.189.camel@jester>
Permalink:  ../1083155014.30065.189.camel@jester/
Also on:    postgresql.org/message-id/1083155014.30065.189.camel@jester

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pg@rbt.ca, mkoi-pg@aon.at, vitalib@012.net.il
  Subject: Re: Simply join in PostrgeSQL takes too long
  In-Reply-To: <1083155014.30065.189.camel@jester>

* 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