> 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
v_genre ALIAS FOR $1;
v_limit integer = 10;
v_transmitted integer = 0;
v_transmitted_values numeric = ARRAY;
FOR t_rows IN SELECT book_id
FROM bv_bookgenres AS b
JOIN bv_genre_children AS g ON (b.genre_id =
WHERE g.genre_id = v_genre
-- 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,
v_transmitted := v_transmitted + 1;
RETURN NEXT t_rows.book_id;
EXIT WHEN v_transmitted >= v_limit;
' LANGUAGE plpgsql;
EXPLAIN ANALYZE SELECT * FROM book_results(1);
SELECT * FROM book_results(1);
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2004-04-28 12:52:20|
|Subject: Re: Join problem |
|Previous:||From: Bruno Wolff III||Date: 2004-04-28 08:32:23|
|Subject: Re: [JDBC] [PERFORM] is a good practice to create an index on the|