CODEWITHSUNDEEP
sqlpostgresqlleft-join
jarosluv
jarosluv

Reputation: 98

How to optimize query with left join

Please, help to optimize my query:

select id, paid_till, rating, paid 
from lots
left join (select 1 paid) paid on current_timestamp <= lots.paid_till 
order by paid asc, rating desc, updated_at desc;

And query plan:

Sort  (cost=1948.17..1948.18 rows=4 width=28) (actual time=0.703..0.704 rows=4 loops=1)
  Sort Key: (1), lots.rating DESC, lots.updated_at DESC
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop Left Join  (cost=0.00..1948.13 rows=4 width=28) (actual time=0.014..0.682 rows=4 loops=1)
     Join Filter: (now() <= lots.paid_till)
     Rows Removed by Join Filter: 2
     ->  Seq Scan on lots  (cost=0.00..1948.04 rows=4 width=24) (actual time=0.008..0.675 rows=4 loops=1)
     ->  Materialize  (cost=0.00..0.03 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

Planning time: 0.210 ms
Execution time: 0.724 ms

What indices I should add? How can I fix "nested loop left join"?

P.S. I can't use virtual column in select for ordering, cause Rails issues.

Upvotes: 0

Views: 335

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

That's a strange way to get the paid value. Use a CASE expression instead:

select 
  id, 
  paid_till, 
  rating, 
  case when current_timestamp <= paid_till then 1 else 0 end as paid 
from lots
order by paid asc, rating desc, updated_at desc;

This proabbly won't speed up the query, though. There is no WHERE clause, so the full table must be read. The only way I see to get this faster, would be a covering index:

create index idx_quick on lots
(
  case when current_timestamp <= paid_till then 1 else 0 end,
  rating,
  updated_at desc,
  paid_till,
  id)
;

But it seems overkill to have an index tailor made for this query.

Upvotes: 2

user330315
user330315

Reputation:

To get rid of the left join use:

select id, paid_till, rating, paid_till is null as paid
from lots
where current_timestamp <= lots.paid_till 
   or paid_till is null
order by 4 asc, rating desc, updated_at desc;

Not sure what you mean with "I can't use virtual column in select for ordering, cause Rails issues" but if your obfuscation layer can't sort on an expression, you could do:

select *
from (
    select id, paid_till, rating, paid_till is null as paid
    from lots
    where current_timestamp <= lots.paid_till 
       or paid_till is null
) t    
order by paid asc, rating desc, updated_at desc;

Or to simply sort all unpaid rows at the end, without using the paid expression at all: 

select id, paid_till, rating
from lots
where current_timestamp <= lots.paid_till 
   or paid_till is null
order by paid_till asc nulls last, rating desc, updated_at desc;

All of the above will get rid of the "Nested Loop Left Join" step. But I really doubt that that is your problem - even on production. The "Seq Scan on lots" for a big table is going to have much more impact.

If the condition current_timestamp <= lots.paid_till only returns a small fraction of that table, putting an index on the paid_till column might help. It would help even more if you can get rid of the or paid_till is null condition. This could be achieved by storing 'infinity' instead of null for those rows that have not yet paid. Without null values in that column the condition can be reduced to where current_timestamp <= lots.paid_till which will make use of an index if that is cheaper then a Seq Scan.

To understand when it makes sense for the optimizer to use an index might you want to read this or this

Upvotes: 1

Related Questions