Обсуждение: How to run this in reasonable time:

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

On Thu, Aug 13, 2009 at 3:16 PM, Matthew Wakeling<matthew@flymine.org> wrote:
> 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;


something like:

select id, objectid, generate_series(s,e) as bin
  from locationbintemp


--
greg
http://mit.edu/~gsstark/resume.pdf

On Thu, 13 Aug 2009, Greg Stark wrote:
> On Thu, Aug 13, 2009 at 3:16 PM, Matthew Wakeling<matthew@flymine.org> wrote:
>> 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;
>
> select id, objectid, generate_series(s,e) as bin
>  from locationbintemp

Thanks. That looks like it shouldn't work, but it does.

Matthew

--
"Beware the lightning that lurketh in an undischarged capacitor, lest it
 cause thee to be bounced upon thy buttocks in a most ungentlemanly manner."
                                -- The Ten Commandments of Electronics