the performance cost is going to be mostly in the kinds of queries it produces and how well they will be interepreted by the query planner. My understanding is that Pony is very heavy on subqueries and correlated subqueries, and the user is given extremely little leverage on being able to control the structure of queries rendered. Subqueries and especially correlated subqueries have the worst performance of all, especially on less mature planners like that of MySQL.
Actually Pony can transform subqueries into JOINs in most of the cases. But when it translates the 'in' operator of a generator expression, it produces a subquery with 'IN', because otherwise the programmer can be confused by the fact that the resulted SQL looks too different from the Python code.
Pony allows you to use the 'JOIN' hint in order to make it to use JOINs instead of subquiries. In the example below Pony produces a subquery when it translates the `in` section from the generator:
>>> from pony.orm.examples.estore import *
>>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:]
SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
FROM "Customer" "c"
WHERE 'iPad' IN (
SELECT "product-1"."name"
FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
WHERE "c"."id" = "order-1"."customer"
AND "order-1"."id" = "orderitem-1"."order"
AND "orderitem-1"."product" = "product-1"."id"
)
But you can tell Pony to use JOIN instead of a subquery by wrapping the 'in' section into a 'JOIN' hint:
>>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:]
SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
WHERE "product-1"."name" = 'iPad'
AND "c"."id" = "order-1"."customer"
AND "order-1"."id" = "orderitem-1"."order"
AND "orderitem-1"."product" = "product-1"."id"
