> Note that the execution time is 46 ms when the query is wrapped in an
> explain analyze (while it's 3 s when it's not!)
Actually, it seems to me that the performance issue is not on the query
itself, it is on the fetching of the data returned by the query. Which
explains why the query is fast when executed in an explain analyze. I've
observed this by using a cursor.
The query returns 2506 rows. I use a cursor to fetch the resulting rows
500 by 500. The fetching of 500 rows (fetch 500 from c) takes about 50
ms in the good/normal case, i.e. when the "select pc_typmod_pcid(1)" is
not executed first. While it takes around 600 ms in the pathological case!
Below is the full test case.
Good case:
psql (9.6.3)
Type "help" for help.
lopocs=# \timing
Timing is on.
lopocs=# begin;
BEGIN
Time: 0.373 ms
lopocs=# declare c cursor for select points from public.sthelens where
pc_intersects(points, st_geomfromtext('polygon ((-2357334.41980829
-3742654.00016992, -2356120.91980829 -3742654.00016992,
-2356120.91980829 -3741278.00016992, -2357334.41980829
-3741278.00016992, -2357334.41980829 -3742654.00016992))', 4978));
DECLARE CURSOR
Time: 75.976 ms
lopocs=# fetch 500 from c;
Time: 44.648 ms
lopocs=# fetch 500 from c;
Time: 40.693 ms
lopocs=# fetch 500 from c;
Time: 45.218 ms
Base case:
psql (9.6.3)
Type "help" for help.
lopocs=# \timing
Timing is on.
lopocs=# select pc_typmod_pcid(1); -- that screws up everything
pc_typmod_pcid
----------------
1
(1 row)
Time: 5.702 ms
lopocs=# begin;
BEGIN
Time: 0.234 ms
lopocs=# declare c cursor for select points from public.sthelens where
pc_intersects(points, st_geomfromtext('polygon ((-2357334.41980829
-3742654.00016992, -2356120.91980829 -3742654.00016992,
-2356120.91980829 -3741278.00016992,
-2357334.41980829 -3741278.00016992, -2357334.41980829
-3742654.00016992))', 4978));
DECLARE CURSOR
Time: 76.806 ms
lopocs=# fetch 500 from c;
Time: 669.834 ms
lopocs=# fetch 500 from c;
Time: 652.738 ms
lopocs=# fetch 500 from c;
Time: 604.293 ms
How can such a thing happen? Thanks for any insight on what could cause
this.
--
Éric Lemoine
Oslandia
+33 1 86 95 95 55