0

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
2

1 Answer 1

0

The Postgres 12 documentation states that offset 0 is the same as omitting the offset clause. However, it also states that: "If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned". Therefore, it might be case that the presence of the limit clause causes offset 0 to be computed despite it being a noop, but the absence causes the query planner to omit the offset 0 hence being much faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.