[Dbix-class] paging question

[James Marca]

On Wed, Feb 03, 2010 at 10:50:10PM +0100, Peter Rabbitson wrote:
> James Marca wrote:
> 
> The only thing that can be prepare_cached are parametrized queries
> (where the query stays the same and only the bindvalues for ? change).
> Limits are currently not expressed via ?'s and it is rather hard to
> do so in SQLA. Let us know if PG supports bind values for the
> limit/offset specification, so we know if an effort is warranted at
> all.

Well, as I said in my earlier email, I'm backtracking.

Yes, postgresql supports parameters for offset and limit, but I'm not sure the effort is warranted.

for example:

spatialvds=# PREPARE test_prep as select * from temp.tempdata order by ts limit $1 offset $2;
PREPARE

spatialvds=# explain analyze execute test_prep(1000,10001);
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3684.62..7369.24 rows=100000 width=374) (actual time=12.552..14.333 rows=1000 loops=1)
   ->  Index Scan using temp_tempdata_ts_idx on tempdata  (cost=0.00..36846.18 rows=1000000 width=374) (actual time=0.077..8.402 rows=11001 loops=1)
 Total runtime: 14.879 ms
(3 rows)

The next batch of rows takes longer only because it has to reel off 21001 values in the scan rather than 11001...

spatialvds=# explain analyze execute test_prep(1000,20001);
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3684.62..7369.24 rows=100000 width=374) (actual time=22.562..24.198 rows=1000 loops=1)
   ->  Index Scan using temp_tempdata_ts_idx on tempdata  (cost=0.00..36846.18 rows=1000000 width=374) (actual time=0.008..13.349 rows=21001 loops=1)
 Total runtime: 24.724 ms
(3 rows)

Which is about the same time required for the bare, unprepared select statement:

spatialvds=# explain analyze select * from temp.tempdata order by ts limit 1000 offset 20001;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=736.96..773.81 rows=1000 width=374) (actual time=22.826..24.496 rows=1000 loops=1)
   ->  Index Scan using temp_tempdata_ts_idx on tempdata  (cost=0.00..36846.18 rows=1000000 width=374) (actual time=0.016..13.635 rows=21001 loops=1)
 Total runtime: 25.022 ms
(3 rows)


I guess it is the same as with any query.  If the inner loop of the
paged query will benefit from preparation, then the paged query will
benefit.  If it is dirt simple like my example, then a prepared
statement won't make much difference.

I think what I was hoping for (before I started looking at this in
detail) was something like a pointer (I think its called a cursor??)
into the original result that just stopped after fetching 'x' rows,
and resumed when the next page was called.  Something like calling the
full query without offset and limit, and doing the offset and limiting
within perl. The first call is expensive (here it costs 1143ms), but
each call to page(n) would reuse any existing db cursor (if
any) from the current result set, and just get the next page-1*rows to
page*rows rows of data.  

And I know that I can do that already by just counting rows with the
usual $rs->next method and stopping when limit is reached.  The
problem is that I *can't* do that because the system runs out of
resources when I use the unpaged query.  Perhaps I need to page by
other means...month by month, etc.

Regards,
James

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.