Thread: Make the planner smarter about idiosyncratic (or broken) ORM behaviour

Almost all of the 8k queries per second that are hitting our database
are generated by a web development framework's ORM (Django), and some
of the syntax there is quite mad. Possibly the worst tic exhibited by
the translation layer is that if you filter a query on an object by
more than one property on a related object it will create a join for
each property:

woome_video=# EXPLAIN ANALYZE SELECT * FROM "webapp_person" INNER JOIN
"auth_user" ON ("webapp_person"."user_id" = "auth_user"."id") INNER
JOIN "auth_user" T3 ON ("webapp_person"."user_id" = T3."id") WHERE
"webapp_person"."is_suspended" = false  AND
"webapp_person"."is_banned" = false  AND ("webapp_person"."is_human" =
true  OR "webapp_person"."is_human" IS NULL) AND
(LOWER("auth_user"."username") = LOWER('d00ditsnicole')  OR
LOWER(T3."first_name") = LOWER('d00ditsnicole') ) AND
"webapp_person"."dob" >= '1910-01-01'  AND "webapp_person"."dob" <=
'1991-01-01'  ORDER BY "auth_user"."last_login" DESC LIMIT 30;



                       QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=0.00..87160.10 rows=30 width=496) (actual
 
time=17641.618..17641.618 rows=0 loops=1)  ->  Nested Loop  (cost=0.00..1095311.97 rows=377 width=496) (actual
time=17641.616..17641.616 rows=0 loops=1)        ->  Nested Loop  (cost=0.00..1090710.92 rows=554 width=230)
(actual time=17641.614..17641.614 rows=0 loops=1)              Join Filter: ((lower((auth_user.username)::text) =
'd00ditsnicole'::text) OR (lower((t3.first_name)::text) =
'd00ditsnicole'::text))              ->  Index Scan Backward using auth_user_last_login_idx
on auth_user  (cost=0.00..141657.02 rows=1684525 width=115) (actual
time=0.039..3315.272 rows=1685757 loops=1)              ->  Index Scan using auth_user_pkey on auth_user t3
(cost=0.00..0.54 rows=1 width=115) (actual time=0.004..0.005 rows=1
loops=1685757)                    Index Cond: (t3.id = auth_user.id)        ->  Index Scan using webapp_person_user_id
onwebapp_person
 
(cost=0.00..8.29 rows=1 width=266) (never executed)              Index Cond: (webapp_person.user_id = auth_user.id)
        Filter: ((NOT webapp_person.is_suspended) AND (NOT
 
webapp_person.is_banned) AND (webapp_person.is_human OR
(webapp_person.is_human IS NULL)) AND (webapp_person.dob >=
'1910-01-01'::date) AND (webapp_person.dob <= '1991-01-01'::date))Total runtime: 17641.871 ms
(11 rows)


There LIMIT and dob filters there are silly as well but they don't
seem to impact query performance; the trouble is the spurious 2nd join
with the T3 alias for auth_user. If I just remove that, we get:

woome_video=# EXPLAIN ANALYZE SELECT * FROM "webapp_person" INNER JOIN
"auth_user" ON ("webapp_person"."user_id" = "auth_user"."id") WHERE
"webapp_person"."is_suspended" = false  AND
"webapp_person"."is_banned" = false  AND ("webapp_person"."is_human" =
true  OR "webapp_person"."is_human" IS NULL) AND
(LOWER("auth_user"."username") = LOWER('d00ditsnicole')  OR
LOWER("auth_user"."first_name") = LOWER('d00ditsnicole') ) AND
"webapp_person"."dob" >= '1910-01-01'  AND "webapp_person"."dob" <=
'1991-01-01'  ORDER BY "auth_user"."last_login" DESC LIMIT 30;

                       QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit
(cost=6637.36..6637.43 rows=30 width=381) (actual
 
time=0.230..0.230 rows=0 loops=1)  ->  Sort  (cost=6637.36..6638.30 rows=377 width=381) (actual
time=0.228..0.228 rows=0 loops=1)        Sort Key: auth_user.last_login        Sort Method:  quicksort  Memory: 25kB
   ->  Nested Loop  (cost=29.88..6626.22 rows=377 width=381)
 
(actual time=0.162..0.162 rows=0 loops=1)              ->  Bitmap Heap Scan on auth_user  (cost=29.88..2025.17
rows=554 width=115) (actual time=0.161..0.161 rows=0 loops=1)                    Recheck Cond:
((lower((username)::text)=
 
'd00ditsnicole'::text) OR (lower((first_name)::text) =
'd00ditsnicole'::text))                    ->  BitmapOr  (cost=29.88..29.88 rows=554
width=0) (actual time=0.158..0.158 rows=0 loops=1)                          ->  Bitmap Index Scan on
woome_username_lower  (cost=0.00..4.60 rows=1 width=0) (actual
time=0.096..0.096 rows=0 loops=1)                                Index Cond: (lower((username)::text)
= 'd00ditsnicole'::text)                          ->  Bitmap Index Scan on
auth_user_firstname_idx  (cost=0.00..25.00 rows=553 width=0) (actual
time=0.060..0.060 rows=0 loops=1)                                Index Cond:
(lower((first_name)::text) = 'd00ditsnicole'::text)              ->  Index Scan using webapp_person_user_id on
webapp_person  (cost=0.00..8.29 rows=1 width=266) (never executed)                    Index Cond:
(webapp_person.user_id= auth_user.id)                    Filter: ((NOT webapp_person.is_suspended) AND
 
(NOT webapp_person.is_banned) AND (webapp_person.is_human OR
(webapp_person.is_human IS NULL)) AND (webapp_person.dob >=
'1910-01-01'::date) AND (webapp_person.dob <= '1991-01-01'::date))Total runtime: 0.448 ms
(16 rows)

So if you do a Person.objects.filter(user__username='foo',
user__first_name='bar') in the ORM, which is filtering Person by
properties on the related user table, then it will create a join for
each of those it seems.

Our Django experts are telling me that it is probably not practical to
fix in the ORM, as it seems to be structural (anyway not fixable for
us in the near term). Hence I am wondering if anyone has an idea as to
how to make the planner smarter about such weirdness (or brokenness);
you might argue that the 2nd join there is merely syntactic bloat
which the planner might just recognize as such?

We are using:

woome_video=# select version();                                            version
-------------------------------------------------------------------------------------------------PostgreSQL 8.3.5 on
x86_64-pc-linux-gnu,compiled by GCC cc (GCC)
 
4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)


Regards,

Frank

Frank Jördens wrote:
> Our Django experts are telling me that it is probably not practical to
> fix in the ORM, as it seems to be structural (anyway not fixable for
> us in the near term). Hence I am wondering if anyone has an idea as to
> how to make the planner smarter about such weirdness (or brokenness);
> you might argue that the 2nd join there is merely syntactic bloat
> which the planner might just recognize as such?

Even if you have funding to hire a developer to adapt PG's planner, it's
going to be an uphill struggle to get patches accepted unless there is a
simple, quick can-merge-two-joins test someone can come up with. Time
spent planning to deal with badly written queries costs every
well-written query too of course.

Even with a patch and acceptance from core, 8.4 is in beta at the moment
so you'll have a long wait before 8.5 comes out with your patch.

Are you sure it wouldn't be easier to hire a Python guru for a couple of
days and have him/her hack the ORM to make it less, um, "simplistic"?
There must be an "assemble references into JOINs" point in the code you
could rationalise this at.

--   Richard Huxton  Archonet Ltd

Frank Joerdens wrote:
> On Fri, May 22, 2009 at 10:56 AM, Richard Huxton <dev@archonet.com> wrote:
> [...]
>> Are you sure it wouldn't be easier to hire a Python guru for a couple of
>> days and have him/her hack the ORM to make it less, um, "simplistic"?
> 
> Not sure. :) Your arguments make eminent sense to me. I am not exactly
> a fan of ORMs, they make my job much harder ... but there seems to be
> no avoiding them these days.

I either like:
1. Really simple, clean ORM but makes it simple to override with custom 
SQL when you need to.
2. Very clever, sophisticated ORM but makes it simple to override with 
custom SQL when you need to.

Unfortunately I'm not a Python guy so I can't comment on Django's ORM.

--   Richard Huxton  Archonet Ltd

On Fri, 2009-05-22 at 10:33 +0100, Frank Jördens wrote:
> Almost all of the 8k queries per second that are hitting our database
> are generated by a web development framework's ORM (Django), and some
> of the syntax there is quite mad. Possibly the worst tic exhibited by
> the translation layer is that if you filter a query on an object by
> more than one property on a related object it will create a join for
> each property:

It's a known issue that we're looking to work on in the next release.

Work started in 8.4, though was more complex than first appeared and we
didn't complete it in time for the dev deadline.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support