How to optimize query with left join

Question

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.

This query takes less then one milli second. How fast do you need that to be? You can't avoid the "nested loop left join" unless you get rid of that useless left join against a constant value. — user330315
– user330315, Commented Oct 12, 2016 at 13:27
If you want real answers you have to show the real explain plan. — Jakub Kania
– Jakub Kania, Commented Oct 12, 2016 at 13:30
It's not the same. I want to get all records, but paid first. Without left join I get only paid. — jarosluv
– jarosluv, Commented Oct 12, 2016 at 13:35
I suppose you could index on paid_till. I believe you would still get a nested loop left join but it would hit the index instead of the table. Since it's a timestamp, I'm not certain you would see any performance improvement. I agree with @a_horse_with_no_name about needing realistic data in your dev table though. The optimizer needs stats on par with production to make the best execution plan. Right now it's making the best plan by using the nested loop left join. Less than 1 millisecond. — JNevill
– JNevill, Commented Oct 12, 2016 at 13:39

Thorsten Kettner · Accepted Answer · 2016-10-12 14:49:59Z

2

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.

answered Oct 12, 2016 at 14:49

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2016-10-12 13:45:54Z

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

Collectives™ on Stack Overflow

How to optimize query with left join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related