Обсуждение: LIMIT causes huge slow down
Hi people,
I'm hitting a strange behavior with our postgres servers : In some cases, using LIMIT causes slowest requests than
withoutLIMIT :
webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre,
galerie_photo.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND
id_flickr=0AND 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_idxon galerie_album (cost=0.42..447.58 rows=255 width=4) (actual time=0.032..0.497
rows=256loops=1) Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18)) -> Index
Scanusing galerie_photo_id_album_idx on galerie_photo (cost=0.43..143.37 rows=59 width=37) (actual time=0.008..0.038
rows=32loops=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND
id_flickr=0AND 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.94rows=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
Backwardusing galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual
time=0.018..3.151rows=1464 loops=1) -> Materialize (cost=0.42..448.86 rows=255 width=4) (actual
time=0.000..0.029rows=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND
id_flickr=0AND 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.94rows=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
Backwardusing galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual
time=0.019..3.072rows=1471 loops=1) -> Materialize (cost=0.42..448.86 rows=255 width=4) (actual
time=0.000..0.030rows=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND
id_flickr=0AND 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.94rows=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
Backwardusing galerie_photo_pkey on galerie_photo (cost=0.43..3056353.78 rows=1553286 width=37) (actual
time=0.019..191.401rows=89385 loops=1) -> Materialize (cost=0.42..448.86 rows=255 width=4) (actual
time=0.000..0.029rows=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND
id_flickr=0AND 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_idxon galerie_album (cost=0.42..283.50 rows=255 width=4) (actual time=0.030..0.433
rows=256loops=1) Index Cond: ((id_picasa = 0) AND (id_flickr = 0) AND (id_webzine = 18)) -> Index
Scanusing galerie_photo_id_album_idx on galerie_photo (cost=0.43..90.21 rows=59 width=37) (actual time=0.008..0.037
rows=32loops=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND
id_flickr=0AND 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=5909width=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) ->
IndexScan 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: 1Total runtime: 6.402 ms
(8 rows)
webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre,
galerie_photo.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND
id_flickr=0AND 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.65rows=5909 width=37) (actual time=6.286..356.395 rows=9 loops=1) -> Index Scan Backward
usinggalerie_photo_pkey on galerie_photo (cost=0.43..1925267.77 rows=1553286 width=37) (actual time=0.020..103.572
rows=89385loops=1) -> Index Scan using galerie_album_pkey on galerie_album (cost=0.29..3.64 rows=1 width=4)
(actualtime=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: 1Total runtime:
356.452ms
(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.
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 withoutLIMIT : > > > webzine=> explain analyze SELECT id_photo, id_album, galerie_photo.etat, dimension, galerie_photo.titre, galerie_photo.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0AND 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) (actualtime=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0AND 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=255width=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0AND 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=255width=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine=18 AND id_flickr=0AND 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=255width=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0AND 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) (actualtime=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0AND 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.002rows=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.dimensionFROM galerie_photo JOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0AND 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.002rows=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. > > >
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.
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_photoJOIN galerie_album USING(id_album) WHERE galerie_album.id_webzine='18' AND id_flickr=0 AND id_picasa=0
ORDERBY 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.30rows=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.51rows=5010 width=37) (actual time=0.087..27.920 rows=8247 loops=1) -> Index Scan using
galerie_album_picasa_flickr_idw_pos_idxon galerie_album (cost=0.42..251.20 rows=225 width=4) (actual time=0.032..0.529
rows=256loops=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.092rows=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.