Optimising postgresql query

I have this query which is rather slow for my liking :

select * from "cams" where 
 "bust" is not null and 
 "figure" is not null and 
 "age" is not null and 
 "hair" is not null and 
 "ethnicity" is not null  
  and "status" = 'online' and  
 "cams"."deleted_at" is null 
 order by ethnicity = 'white'  DESC, 
  age = 22 DESC, 
 (age >= 18 AND age <= 35) DESC, 
 bust = 'medium' DESC, 
 figure = 'petite' DESC, 
 hair = 'blonde' DESC 
 limit 10

​x
 
select * from "cams" where  "bust" is not null and  "figure" is not null and  "age" is not null and  "hair" is not null and  "ethnicity" is not null    and "status" = 'online' and   "cams"."deleted_at" is null  order by ethnicity = 'white'  DESC,   age = 22 DESC,  (age >= 18 AND age <= 35) DESC,  bust = 'medium' DESC,  figure = 'petite' DESC,  hair = 'blonde' DESC  limit 10​

Explain analyse output

 Limit  (cost=10045.82..10045.84 rows=10 width=318) (actual time=754.187..754.190 rows=10 loops=1)
   ->  Sort  (cost=10045.82..10047.15 rows=532 width=318) (actual time=754.182..754.183 rows=10 loops=1)
         Sort Key: ((ethnicity = 'white'::ethnicity)) DESC, ((age = 22)) DESC, (((age >= 18) AND (age <= 35))) DESC, ((bust = 'medium'::bust)) DESC, ((figure = 'petite'::figure)) DESC, ((hair = 'blonde'::hair_color)) DESC
         Sort Method: top-N heapsort  Memory: 33kB
         ->  Bitmap Heap Scan on cams  (cost=1328.15..10034.32 rows=532 width=318) (actual time=580.008..745.590 rows=5092 loops=1)
               Recheck Cond: ((hair IS NOT NULL) AND (age IS NOT NULL) AND (status = 'online'::cam_status))
               Filter: ((bust IS NOT NULL) AND (figure IS NOT NULL) AND (ethnicity IS NOT NULL) AND (deleted_at IS NULL))
               Rows Removed by Filter: 2414
               Heap Blocks: exact=49643
               ->  Bitmap Index Scan on cams_online_rank_age  (cost=0.00..1328.02 rows=2406 width=0) (actual time=567.587..567.587 rows=4715231 loops=1)
                     Index Cond: ((hair IS NOT NULL) AND (age IS NOT NULL))
 Planning Time: 1.526 ms
 Execution Time: 754.464 ms

 
 Limit  (cost=10045.82..10045.84 rows=10 width=318) (actual time=754.187..754.190 rows=10 loops=1)   ->  Sort  (cost=10045.82..10047.15 rows=532 width=318) (actual time=754.182..754.183 rows=10 loops=1)         Sort Key: ((ethnicity = 'white'::ethnicity)) DESC, ((age = 22)) DESC, (((age >= 18) AND (age <= 35))) DESC, ((bust = 'medium'::bust)) DESC, ((figure = 'petite'::figure)) DESC, ((hair = 'blonde'::hair_color)) DESC         Sort Method: top-N heapsort  Memory: 33kB         ->  Bitmap Heap Scan on cams  (cost=1328.15..10034.32 rows=532 width=318) (actual time=580.008..745.590 rows=5092 loops=1)               Recheck Cond: ((hair IS NOT NULL) AND (age IS NOT NULL) AND (status = 'online'::cam_status))               Filter: ((bust IS NOT NULL) AND (figure IS NOT NULL) AND (ethnicity IS NOT NULL) AND (deleted_at IS NULL))               Rows Removed by Filter: 2414               Heap Blocks: exact=49643               ->  Bitmap Index Scan on cams_online_rank_age  (cost=0.00..1328.02 rows=2406 width=0) (actual time=567.587..567.587 rows=4715231 loops=1)                     Index Cond: ((hair IS NOT NULL) AND (age IS NOT NULL)) Planning Time: 1.526 ms Execution Time: 754.464 ms​​

Is there an index I can put on to speed this up (bearing in mind that the values for the order by will be dynamic)? I was thinking a partial index on where bust,figure,age,hair ethnicity is not null and status = ‘online’ but then not sure what column to rank on as the order by is dynamic (I’m trying to find the post similar item to a certain item).

Answer

Your only hope is an index on (status, deleted_at). That at least matches the equality conditions in the where clause. If you know that another column has a high proportion of null values, you can include that as a third key.

This may limit the scanning of the table. However, the performance may be more based on the sorting for the order by than scanning the table.

Advertisement

Answer