I'm trying to execute a query to take a row from a table, and return
multiple rows, one per integer in the range between two of the fields in
that row, for all rows in the table. Perhaps a better explanation would be
the query:
SELECT id, objectid, bin
FROM locationbintemp, generate_series(0, 100000) AS bin
WHERE s <= bin AND e >= bin;
Now, this query is planned as a horrendous nested loop. For each row in
the source table, it will iterate through 100000 rows of generate_series
to find the couple of rows which match.
QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop (cost=0.00..110890441.22 rows=447791333 width=12)
Join Filter: ((locationbintemp.s <= bin.bin) AND (locationbintemp.e >= bin.bin))
-> Seq Scan on locationbintemp (cost=0.00..62086.22 rows=4030122 width=16)
-> Function Scan on generate_series bin (cost=0.00..12.50 rows=1000 width=4)
(4 rows)
Now, I'd like to get this done this side of Christmas, so I was wondering
if there's a neat trick I can use to get it to only consider the rows from
s to e, instead of having to iterate through them all. I tried this, but
got an error message:
SELECT id, objectid, bin
FROM locationbintemp, generate_series(s, e) AS bin;
ERROR: function expression in FROM cannot refer to other relations of same query level
LINE 1: ...jectid, bin FROM locationbintemp, generate_series(s, e) AS b...
Any help appreciated.
Matthew
--
If you're thinking "Oh no, this lecturer thinks Turing Machines are a feasible
method of computation, where's the door?", then you are in luck. There are
some there, there, and by the side there. Oxygen masks will not drop from the
ceiling... -- Computer Science Lecturer
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
