How to run this in reasonable time:

От: Matthew Wakeling
Тема: How to run this in reasonable time:
Дата: ,
Msg-id: alpine.DEB.2.00.0908131511100.19472@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответы: Re: How to run this in reasonable time:  (Greg Stark)
Список: 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


В списке pgsql-performance по дате сообщения:

От: Richard Huxton
Дата:
Сообщение: Re: Under the hood of views
От: Rstat
Дата:
Сообщение: Less expensive proprietary or Open source ETL tools