Try basing the interval query on a correlated subquery, so instead of q = session.query( column, func.row_number().\ over(order_by=column).\ label('rownum') ).\ from_self(column)
try subquery = base_query.subquery() q = session.query( column, func.row_number().\ over(order_by=column).\ label('rownum') ).\ join(subquery, subquery.columns[column.name] == column).\ from_self(column) This should give a SQL statement of SELECT anon_1.widget_id AS anon_1_widget_id FROM ( SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS rownum FROM widget JOIN ( SELECT widget.id AS id, widget.data AS data FROM widget WHERE widget.id < :id_1) AS anon_2 ON anon_2.id = widget.id) AS anon_1 WHERE rownum % 1000=1 where base_query is a filtered query on the widget table, in this example I used the following to take the first half of the widget table base_query = session.query(Widget).filter(Widget.id < 5000) For reference, the SQL statement in the original post is SELECT anon_1.widget_id AS anon_1_widget_id FROM ( SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS rownum FROM widget) AS anon_1 WHERE rownum % 1000=1 On Tuesday, August 5, 2014 5:15:06 PM UTC+1, Jonathan Vanasco wrote: > > > > On Monday, August 4, 2014 11:59:36 PM UTC-4, Michael Bayer wrote: >> >> >> ah because ROW_NUMBER() needs…the row number! that is, we need to count >> the rows between PK values. Well if you have truly increment-by-one >> primary keys and that’s what you’re windowing on, you can make that the >> “rownum”…otherwise, you’re looking for windows of N rows within the whole >> set of rows. Counting is necessary, I’m not sure how to force it to use an >> index-only scan to figure this out. I haven’t looked into this that >> deeply. >> > > Yeah, I played with it for a while and dug deep into some psql listserves > and dba forums trying to trick it. i might jump on the postgres list for > some insight next. > > >> add a WHERE clause to the query that is selecting the windows, that is >> instead of: >> > > i ended up cobbling one together to do that. though to eek out a bit of > better performance, i used a series of temp tables. > > i was just wondering if anyone had pulled together a drop-in recipe yet > that inspected the base-query for any filters, and pushed that into the > 'select' that generates the iterables. > > > > > >> > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.