pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMake the planner smarter about idiosyncratic (or broken) ORM behaviour
4+ messages / 3 participants
[nested] [flat]
* Make the planner smarter about idiosyncratic (or broken) ORM behaviour
@ 2009-05-22 09:33 Frank Jördens <frank@woome.com>
0 siblings, 2 replies; 4+ messages in thread
From: Frank Jördens @ 2009-05-22 09:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: Nic Ferrier <nferrier@woome.com>; Edward George <ed@woome.com>; Seb Potter <seb@woome.com>; Hannu Krosing <hannu@2ndquadrant.com>
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 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: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Make the planner smarter about idiosyncratic (or broken) ORM behaviour
@ 2009-05-22 09:56 Richard Huxton <dev@archonet.com>
parent: Frank Jördens <frank@woome.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2009-05-22 09:56 UTC (permalink / raw)
To: Frank Jördens <frank@woome.com>; +Cc: pgsql-sql@postgresql.org, Nic Ferrier <nferrier@woome.com>; Edward George <ed@woome.com>; Seb Potter <seb@woome.com>; Hannu Krosing <hannu@2ndquadrant.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Make the planner smarter about idiosyncratic (or broken) ORM behaviour
@ 2009-05-22 10:33 Richard Huxton <dev@archonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2009-05-22 10:33 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql@postgresql.org, Nic Ferrier <nferrier@woome.com>; Edward George <ed@woome.com>; Seb Potter <seb@woome.com>; Hannu Krosing <hannu@2ndquadrant.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Make the planner smarter about idiosyncratic (or broken) ORM behaviour
@ 2009-05-22 10:40 Simon Riggs <simon@2ndQuadrant.com>
parent: Frank Jördens <frank@woome.com>
1 sibling, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2009-05-22 10:40 UTC (permalink / raw)
To: Frank Jördens <frank@woome.com>; +Cc: pgsql-sql@postgresql.org, Nic Ferrier <nferrier@woome.com>; Edward George <ed@woome.com>; Seb Potter <seb@woome.com>; Hannu Krosing <hannu@2ndquadrant.com>
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.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-05-22 10:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-22 09:33 Make the planner smarter about idiosyncratic (or broken) ORM behaviour Frank Jördens <frank@woome.com>
2009-05-22 09:56 ` Re: Make the planner smarter about idiosyncratic (or broken) ORM behaviour Richard Huxton <dev@archonet.com>
2009-05-22 10:33 ` Richard Huxton <dev@archonet.com>
2009-05-22 10:40 ` Simon Riggs <simon@2ndQuadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox