[postgis-users] Severe performance problems with prepared statements

[William Becker]

I just tried changing my code base from using hard coded queries to
prepared statements. As part of this I pass a parameter as the argument to
the ST_GeographyFromText() function. You can see the code here (1).

After doing this, it took ~20 seconds to run, when previously it took ~2
seconds. I thought this might be because it wasn't using an index anymore,
but I checked this out - when it was previously taking 2 seconds, it also
ran without using the index. For some reason it's using it on my dev
machine, but this has a smaller dataset - I'll try tuning this but it does
not seem to be the cause of the issue.

I "improved" this by templatising the query, so that a new query is always
generated with a different hard coded geography, and this speeds things up
- code is here (2). However, it now needs to create a separate query for
each execution, thus it also needs to generate a new plan every time, which
is not ideal.

I have attached query plans for the slow run (3) and the sped up run (4).
(I apologise that the queries aren't exactly the same in each case, but the
timings are pretty much the same regardless of what the other parameters
are).

You can also see the create statement for the database here: (5)

I'm not entirely sure if the problem lies with postgis or postgres in
general, but I thought I'd present it to you nice people first, since you
are probably in a better position to decide! This is on postgis 2.0 with
postgres 9.2. I just upgraded to 9.3 and 2.1 but I need to re-process all
my data (which will take overnight) before I can do anything.

My guess is that it is evalutating the parameter each time it applies the
where in the query and there is something slow about this. I'd poke around
the relavant code if someone could give me some pointers about where to
look!

I've seem some other threads that seem to be related:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
http://grokbase.com/t/postgresql/pgsql-performance/127rp189dw/geoserver-postgis-performance-problems

but it seems that I have isolated it just down to that scan taking a long
time with the prepared statement, but quick without it?

Cheers,
Will

(1)
https://github.com/twistedvisions/anaximander/blob/c0a7c89e438bcf2504ed8ae3d5f75828e11c2ec9/db_templates/get_event_attendees_in_area.sql

(2)
https://github.com/twistedvisions/anaximander/blob/12e65537f1d3887677b592d1f0ac0203f2da7d39/db_templates/get_event_attendees_in_area.sql

(3) http://explain.depesz.com/s/kb1A

(4) http://explain.depesz.com/s/G9Wj

(5)
https://github.com/twistedvisions/anaximander/blob/f6ab21ebff4cf3541bfb53715addb4b3181d5d2e/db/create.sql
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140105/e0f39179/attachment.html>