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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
