LIMIT causes huge slow down

hello

i am just a lowly application developer, but i always include my 'where'
fields in my order by specification.
I.E. ORDER BY id_webzine,id_flickr,id_picasa,id_photo rather then just
id_photo

On 3/12/2014 3:38 AM, Gr�gory Giannoni wrote:

Hi people,

I'm hitting a strange behavior with our postgres servers : In some cases, using LIMIT causes slowest requests than without LIMIT :

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=37527.48..37542.25 rows=5909 width=37) (actual time=19.742..21.066 rows=8247 loops=1)
Sort Key: galerie_photo.id_photo
Sort Method: quicksort Memory: 1186kB
-> Nested Loop (cost=0.84..37157.32 rows=5909 width=37) (actual time=0.044..14.104 rows=8247 loops=1)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..447.58 rows=255 width=4) (actual time=0.032..0.497 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
-> Index Scan using galerie_photo_id_album_idx on galerie_photo (cost=0.43..143.37 rows=59 width=37) (actual time=0.008..0.038 rows=32 loops=256)
Index Cond: (id_album = galerie_album.id_album)
Total runtime: 22.003 ms
(9 rows)

Adding LIMIT 1 modifies the query plan and slow downs...

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.84..1523.63 rows=1 width=37) (actual time=115.787..115.787 rows=1 loops=1)
-> Nested Loop (cost=0.84..8998120.94 rows=5909 width=37) (actual time=115.787..115.787 rows=1 loops=1)
Join Filter: (galerie_photo.id_album = galerie_album.id_album)
Rows Removed by Join Filter: 374528
-> Index Scan Backward using galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual time=0.018..3.151 rows=1464 loops=1)
-> Materialize (cost=0.42..448.86 rows=255 width=4) (actual time=0.000..0.029 rows=256 loops=1464)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..447.58 rows=255 width=4) (actual time=0.032..0.458 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
Total runtime: 115.835 ms
(9 rows)

increasing the LIMIT parameter up to 8 don't change anything :

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC LIMIT 8;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.84..12183.10 rows=8 width=37) (actual time=115.853..116.370 rows=8 loops=1)
-> Nested Loop (cost=0.84..8998120.94 rows=5909 width=37) (actual time=115.852..116.369 rows=8 loops=1)
Join Filter: (galerie_photo.id_album = galerie_album.id_album)
Rows Removed by Join Filter: 376313
-> Index Scan Backward using galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual time=0.019..3.072 rows=1471 loops=1)
-> Materialize (cost=0.42..448.86 rows=255 width=4) (actual time=0.000..0.030 rows=256 loops=1471)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..447.58 rows=255 width=4) (actual time=0.032..0.525 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
Total runtime: 116.420 ms
(9 rows)

But passing LIMIT 9 :

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC LIMIT 9;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.84..13705.88 rows=9 width=37) (actual time=118.209..6905.990 rows=9 loops=1)
-> Nested Loop (cost=0.84..8998120.94 rows=5909 width=37) (actual time=118.207..6905.985 rows=9 loops=1)
Join Filter: (galerie_photo.id_album = galerie_album.id_album)
Rows Removed by Join Filter: 22882297
-> Index Scan Backward using galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual time=0.019..191.401 rows=89385 loops=1)
-> Materialize (cost=0.42..448.86 rows=255 width=4) (actual time=0.000..0.029 rows=256 loops=89385)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..447.58 rows=255 width=4) (actual time=0.033..0.464 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
Total runtime: 6906.050 ms
(9 rows)

Reducing the random page cost to 2.5 (I'm on SSD drives) can improve this behavior :

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=23807.38..23822.15 rows=5909 width=37) (actual time=19.163..20.660 rows=8247 loops=1)
Sort Key: galerie_photo.id_photo
Sort Method: quicksort Memory: 1186kB
-> Nested Loop (cost=0.84..23437.22 rows=5909 width=37) (actual time=0.040..13.553 rows=8247 loops=1)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..283.50 rows=255 width=4) (actual time=0.030..0.433 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
-> Index Scan using galerie_photo_id_album_idx on galerie_photo (cost=0.43..90.21 rows=59 width=37) (actual time=0.008..0.037 rows=32 loops=256)
Index Cond: (id_album = galerie_album.id_album)
Total runtime: 21.554 ms
(9 rows)

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.72..1284.78 rows=1 width=37) (actual time=6.356..6.356 rows=1 loops=1)
-> Nested Loop (cost=0.72..7587529.65 rows=5909 width=37) (actual time=6.356..6.356 rows=1 loops=1)
-> Index Scan Backward using galerie_photo_pkey on galerie_photo (cost=0.43..1925267.77 rows=1553286 width=37) (actual time=0.026..2.123 rows=1464 loops=1)
-> Index Scan using galerie_album_pkey on galerie_album (cost=0.29..3.64 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1464)
Index Cond: (id_album = galerie_photo.id_album)
Filter: ((id_webzine = 18) AND (id_flickr = 0) AND (id_picasa = 0))
Rows Removed by Filter: 1
Total runtime: 6.402 ms
(8 rows)

webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC LIMIT 9;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.72..11557.29 rows=9 width=37) (actual time=6.286..356.399 rows=9 loops=1)
-> Nested Loop (cost=0.72..7587529.65 rows=5909 width=37) (actual time=6.286..356.395 rows=9 loops=1)
-> Index Scan Backward using galerie_photo_pkey on galerie_photo (cost=0.43..1925267.77 rows=1553286 width=37) (actual time=0.020..103.572 rows=89385 loops=1)
-> Index Scan using galerie_album_pkey on galerie_album (cost=0.29..3.64 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=89385)
Index Cond: (id_album = galerie_photo.id_album)
Filter: ((id_webzine = 18) AND (id_flickr = 0) AND (id_picasa = 0))
Rows Removed by Filter: 1
Total runtime: 356.452 ms
(8 rows)

The tables have been vacuumed, analyzed and reindexed without any change. results are reproductibles.
galerie_album has 67033 rows (256 of them have id_webzine=18)
galerie_photo has 1494738 rows

Any idea or workaround ?

Regards,

Gr�gory Giannoni.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Harry Rossignol wrote

hello

i am just a lowly application developer, but i always include my 'where'
fields in my order by specification.
I.E. ORDER BY id_webzine,id_flickr,id_picasa,id_photo rather then just
id_photo

You really should consider thinking about what you need rather than blindly
adhering to a rule that puts additional burden on the system when it may not
be necessary.

WRT to question posed: probably the easiest workaround is to move the
unlimited query to a WITH clause and then apply the limit separately.

You should indicate what version of PostgreSQL you are using.

Sorry I'm not much help on the how and why of the actual plan choices here.
The sorting is constant but since the limit and the where clause target
different tables a full evaluation is needed to determine a solution so
picking individual rows, which is what I see happening, doesn't by you
anything.

But, for all I know your using an old version and this undesirable behavior
has already been found and fixed.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/LIMIT-causes-huge-slow-down-tp5795640p5795717.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Le 12 mars 2014 à 17:34, David Johnston a écrit :

Harry Rossignol wrote

hello

i am just a lowly application developer, but i always include my 'where'
fields in my order by specification.
I.E. ORDER BY id_webzine,id_flickr,id_picasa,id_photo rather then just
id_photo

[...]
WRT to question posed: probably the easiest workaround is to move the
unlimited query to a WITH clause and then apply the limit separately.

You should indicate what version of PostgreSQL you are using.

Sorry I'm not much help on the how and why of the actual plan choices here.
The sorting is constant but since the limit and the where clause target
different tables a full evaluation is needed to determine a solution so
picking individual rows, which is what I see happening, doesn't by you
anything.

But, for all I know your using an old version and this undesirable behavior
has already been found and fixed.

Hi,

thank you for your answer. I'm using PostgreSQL 9.3.3.

Anyway, I tried Harry's idea.. and the result is for me very surprising : it worked.

=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, galerie_photo.titre, galerie_photo.dimension FROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0 ORDER BY id_photo DESC, id_album DESC LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20388.78..20388.80 rows=10 width=37) (actual time=31.402..31.406 rows=10 loops=1)
-> Sort (cost=20388.78..20401.30 rows=5010 width=37) (actual time=31.400..31.401 rows=10 loops=1)
Sort Key: galerie_photo.id_photo, galerie_photo.id_album
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=0.84..20280.51 rows=5010 width=37) (actual time=0.087..27.920 rows=8247 loops=1)
-> Index Scan using galerie_album_picasa_flickr_idw_pos_idx on galerie_album (cost=0.42..251.20 rows=225 width=4) (actual time=0.032..0.529 rows=256 loops=1)
Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18))
-> Index Scan using galerie_photo_id_album_idx on galerie_photo (cost=0.43..88.45 rows=57 width=37) (actual time=0.019..0.092 rows=32 loops=256)
Index Cond: (id_album = galerie_album.id_album)
Total runtime: 31.465 ms
(10 rows)

Adding a second order clause make the query planner choose the same plan that without the LIMIT, and perfs are OK.

It is against my thoughts that adding sort element necessary slow down the process... but it work (for this particular case).

Best regards,

Grégory Giannoni.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs