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
Last activity: Oct 12, 2024, 07:08 PM