Sample Header Ad - 728x90

PostgreSQL is skipping indexes when using `order by` + `limit`

-1 votes
1 answer
915 views
I have a postgreSQL database with two big tables, connections and sessions
select "c"."id" "id", "ch"."title" "channel", "ch"."name" "channel_name", "c"."nickname", "c"."duration", "c"."status", "c"."start_time", "c"."stop_time", "u"."nickname" "user", "u"."username" "username"
from "connections" "c"
left join "sessions" "sh" on "session_id" = "sh"."id"
left join "customers" "cs" on "sh"."customer_id" = "cs"."id"
left join "channels" "ch" on "channel_id" = "ch"."id"
left join "clients" "cl" on "client_id" = "cl"."id"
left join "users" "u" on "c"."user_id" = "u"."id"
where ("sh"."customer_id" = 13598)
order by "id" desc 
limit 10 offset 0
When running this query without limit, execution takes about 800ms. But with limit 10 it takes 16000ms!!! It seems that when limit added, Postgre skips indexes. How can I change that? This is the explain result of this query with and without the limit: **With limit:**
Limit  (cost=30.93..30.94 rows=1 width=1202) (actual time=0.017..0.017 rows=0 loops=1)
  ->  Sort  (cost=30.93..30.94 rows=1 width=1202) (actual time=0.015..0.015 rows=0 loops=1)
        Sort Key: c.id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop Left Join  (cost=14.02..30.92 rows=1 width=1202) (actual time=0.009..0.009 rows=0 loops=1)
              ->  Nested Loop Left Join  (cost=13.88..30.49 rows=1 width=850) (actual time=0.008..0.008 rows=0 loops=1)
                    ->  Hash Join  (cost=13.74..26.14 rows=1 width=306) (actual time=0.008..0.008 rows=0 loops=1)
                          Hash Cond: (c.session_id = sh.id)
                          ->  Seq Scan on skyroom_connections c  (cost=0.00..11.90 rows=190 width=310) (actual time=0.007..0.007 rows=0 loops=1)
                          ->  Hash  (cost=13.67..13.67 rows=6 width=12) (never executed)
                                ->  Bitmap Heap Scan on skyroom_sessions sh  (cost=4.20..13.67 rows=6 width=12) (never executed)
                                      Recheck Cond: (customer_id = 13598)
                                      ->  Bitmap Index Scan on skyroom_sessions_customer_idx  (cost=0.00..4.20 rows=6 width=0) (never executed)
                                            Index Cond: (customer_id = 13598)
                    ->  Index Scan using skyroom_channels_pkey on skyroom_channels ch  (cost=0.14..4.16 rows=1 width=552) (never executed)
                          Index Cond: (id = sh.channel_id)
              ->  Index Scan using users_pkey on users u  (cost=0.14..0.38 rows=1 width=360) (never executed)
                    Index Cond: (id = c.user_id)
Planning Time: 0.978 ms
Execution Time: 0.147 ms
**Without Limit:**
Sort  (cost=30.93..30.94 rows=1 width=1202) (actual time=0.010..0.010 rows=0 loops=1)
  Sort Key: c.id DESC
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop Left Join  (cost=14.02..30.92 rows=1 width=1202) (actual time=0.004..0.004 rows=0 loops=1)
        ->  Nested Loop Left Join  (cost=13.88..30.49 rows=1 width=850) (actual time=0.004..0.004 rows=0 loops=1)
              ->  Hash Join  (cost=13.74..26.14 rows=1 width=306) (actual time=0.004..0.004 rows=0 loops=1)
                    Hash Cond: (c.session_id = sh.id)
                    ->  Seq Scan on skyroom_connections c  (cost=0.00..11.90 rows=190 width=310) (actual time=0.003..0.003 rows=0 loops=1)
                    ->  Hash  (cost=13.67..13.67 rows=6 width=12) (never executed)
                          ->  Bitmap Heap Scan on skyroom_sessions sh  (cost=4.20..13.67 rows=6 width=12) (never executed)
                                Recheck Cond: (customer_id = 13598)
                                ->  Bitmap Index Scan on skyroom_sessions_customer_idx  (cost=0.00..4.20 rows=6 width=0) (never executed)
                                      Index Cond: (customer_id = 13598)
              ->  Index Scan using skyroom_channels_pkey on skyroom_channels ch  (cost=0.14..4.16 rows=1 width=552) (never executed)
                    Index Cond: (id = sh.channel_id)
        ->  Index Scan using users_pkey on users u  (cost=0.14..0.38 rows=1 width=360) (never executed)
              Index Cond: (id = c.user_id)
Planning Time: 0.425 ms
Execution Time: 0.097 ms
Asked by rostamiani (191 rep)
Aug 3, 2020, 05:36 PM
Last activity: Oct 12, 2024, 07:08 PM