From: | Matthew Wakeling <matthew(at)flymine(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | How to run this in reasonable time: |
Date: | 2009-08-13 14:16:13 |
Message-ID: | alpine.DEB.2.00.0908131511100.19472@aragorn.flymine.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2009-08-13 14:25:31 | Re: How to run this in reasonable time: |
Previous Message | Nickolay | 2009-08-13 13:31:29 | Re: transaction delays to apply |