search

melinath / django-daguerre

On-the-fly image manipulation for Django.
http://django-daguerre.readthedocs.org/
BSD 3-Clause "New" or "Revised" License
85 stars 15 forks source link

adding composite index on AdjustedImage model #76

Closed mislavcimpersak closed 8 years ago

mislavcimpersak commented 8 years ago

I'm using django-daguerre in a project that currently has ~980.000 images in storage and all images have at least 3 variations and some much more.

Adding composite index to AdjustedImage model really speeds things up when retrieving image variations.

melinath commented 8 years ago

It's great to hear that someone's using django-daguerre on a project of that size! Thanks for taking the time to create a PR. This is a great improvement. OOC, did you happen to see a speed improvement by defining the index as ['requested', 'storage_path'] rather than ['storage_path', 'requested']?

mislavcimpersak commented 8 years ago

Sorry, I haven't run such test.

I just have the timings before and after the index (and this is while there were a lot less then 980.000 images in the db):

db=# explain analyze SELECT "daguerre_adjustedimage"."id", "daguerre_adjustedimage"."storage_path", "daguerre_adjustedimage"."adjusted" FROM "daguerre_adjustedimage" WHERE  ("daguerre_adjustedimage"."requested" = 'fill|943|531||' AND "daguerre_adjustedimage"."storage_path" = 'images/src/20090936/230793.jpg');
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on daguerre_adjustedimage  (cost=0.00..12885.24 rows=1 width=85) (actual time=21.400..99.425 rows=1 loops=1)
   Filter: (((requested)::text = 'fill|943|531||'::text) AND ((storage_path)::text = 'images/src/20090936/230793.jpg'::text))
   Rows Removed by Filter: 375275
 Total runtime: 99.438 ms
(4 rows)

db=# explain analyze SELECT "daguerre_adjustedimage"."id", "daguerre_adjustedimage"."storage_path", "daguerre_adjustedimage"."adjusted" FROM "daguerre_adjustedimage" WHERE  ("daguerre_adjustedimage"."requested" = 'fill|943|531||' AND "daguerre_adjustedimage"."storage_path" = 'images/src/20090936/230793.jpg');
                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using daguerre_adjustedimage_requested_26a549f5ee5b7539_idx on daguerre_adjustedimage  (cost=0.42..4.44 rows=1 width=85) (actual time=0.083..0.083 rows=1 loops=1)
   Index Cond: (((requested)::text = 'fill|943|531||'::text) AND ((storage_path)::text = 'images/src/20090936/230793.jpg'::text))
 Total runtime: 0.096 ms
(3 rows)
melinath commented 8 years ago

That's a pretty impressive speedup. Yay indexes!