CODEWITHSUNDEEP
sqlpostgresqljoin
Greedy Coder
Greedy Coder

Reputation: 1326

Order by with left join not using indexes and very slow

I have the following two queries.Query 1 is fast since it uses indexes(uses nested loop join) and Query 2 uses hash join and it is slower.

Query 1 does order by on table 1 column and Query 2 does order by using table 2 column.

Query 1

learning=# explain analyze
select *
from users left join
     access_logs
     on users.userid = access_logs.userid
order by users.userid
limit 10 offset 90;


                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=14.00..15.46 rows=10 width=104) (actual time=1.330..1.504 rows=10 loops=1)
   ->  Merge Left Join  (cost=0.85..291532.97 rows=1995958 width=104) (actual time=0.037..1.482 rows=100 loops=1)
         Merge Cond: (users.userid = access_logs.userid)
         ->  Index Scan using users_pkey on users  (cost=0.43..151132.75 rows=1995958 width=76) (actual time=0.018..1.135 rows=100 loops=1)
         ->  Index Scan using access_logs_userid_idx on access_logs  (cost=0.43..110471.45 rows=1995958 width=28) (actual time=0.012..0.198 rows=100 loops=1)
 Planning time: 0.469 ms
 Execution time: 1.569 ms

Query 2

learning=# explain analyze
select *
from users left join
     access_logs
     on users.userid = access_logs.userid
order by access_logs.userid
limit 10 offset 90;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=293584.20..293584.23 rows=10 width=104) (actual time=3821.432..3821.439 rows=10 loops=1)
   ->  Sort  (cost=293583.98..298573.87 rows=1995958 width=104) (actual time=3821.391..3821.415 rows=100 loops=1)
         Sort Key: access_logs.userid
         Sort Method: top-N heapsort  Memory: 51kB
         ->  Hash Left Join  (cost=73231.06..217299.90 rows=1995958 width=104) (actual time=539.859..3168.754 rows=1995958 loops=1)
               Hash Cond: (users.userid = access_logs.userid)
               ->  Seq Scan on users  (cost=0.00..44814.58 rows=1995958 width=76) (actual time=0.009..443.260 rows=1995958 loops=1)
               ->  Hash  (cost=34636.58..34636.58 rows=1995958 width=28) (actual time=539.112..539.112 rows=1995958 loops=1)
                     Buckets: 262144  Batches: 2  Memory Usage: 58532kB
                     ->  Seq Scan on access_logs  (cost=0.00..34636.58 rows=1995958 width=28) (actual time=0.006..170.061 rows=1995958 loops=1)
 Planning time: 0.480 ms
 Execution time: 3832.245 ms

Questions

Query - explain analyze select * from access_logs order by userid limit 10 offset 90;

Plan

 Limit  (cost=5.41..5.96 rows=10 width=28) (actual time=0.199..0.218 rows=10 loops=1)
   ->  Index Scan using access_logs_userid_idx on access_logs  (cost=0.43..110471.45 rows=1995958 width=28) (actual time=0.029..0.201 rows=100 loops=1)
 Planning time: 0.120 ms
 Execution time: 0.252 ms

Edit 1:

My goal is not to compare both queries, in fact I want the result as in query 2,I just provided query 1 so that I can understand in comparison.

The order by is not limited to the join column, the user can also do order by another column in table 2, plans are below.

learning=# explain analyze select * from users left join access_logs on users.userid=access_logs.userid order by access_logs.last_login limit 10;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=260431.83..260431.86 rows=10 width=104) (actual time=3846.625..3846.627 rows=10 loops=1)
   ->  Sort  (cost=260431.83..265421.73 rows=1995958 width=104) (actual time=3846.623..3846.623 rows=10 loops=1)
         Sort Key: access_logs.last_login
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Hash Left Join  (cost=73231.06..217299.90 rows=1995958 width=104) (actual time=567.104..3174.818 rows=1995958 loops=1)
               Hash Cond: (users.userid = access_logs.userid)
               ->  Seq Scan on users  (cost=0.00..44814.58 rows=1995958 width=76) (actual time=0.007..443.364 rows=1995958 loops=1)
               ->  Hash  (cost=34636.58..34636.58 rows=1995958 width=28) (actual time=566.814..566.814 rows=1995958 loops=1)
                     Buckets: 262144  Batches: 2  Memory Usage: 58532kB
                     ->  Seq Scan on access_logs  (cost=0.00..34636.58 rows=1995958 width=28) (actual time=0.004..169.137 rows=1995958 loops=1)
 Planning time: 0.490 ms
 Execution time: 3857.171 ms

Upvotes: 0

Views: 1313

Answers (1)

Y.B.
Y.B.

Reputation: 3586

Sort in the second query would not use index because the index is not guaranteed to have all the values being sorted. If there are some records in users not matched by access_logs then Left Join would generate null values referenced in query as access_logs.userid but not actually present in access_logs and thus not covered by the index.

The workaround can be to create a default initial record in access_log for each user and use Inner Join.

Upvotes: 2

Related Questions