Question 397730 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Worse performance when using IN query

This query never finishes:

SELECT      *
FROM        logs
WHERE       user_id IN (1,2,3,4,5)
ORDER BY    create_time DESC
OFFSET      0 ROWS 
FETCH FIRST 10 ROWS only

But it works fast when I run query for each user:

SELECT      *
FROM        logs
WHERE       user_id = 1
ORDER BY    create_time DESC
OFFSET      0 ROWS 
FETCH FIRST 10 ROWS only

SELECT      *
FROM        logs
WHERE       user_id = 2
ORDER BY    create_time DESC
OFFSET      0 ROWS 
FETCH FIRST 10 ROWS only

SELECT      *
FROM        logs
WHERE       user_id = 3
ORDER BY    create_time DESC
OFFSET      0 ROWS 
FETCH FIRST 10 ROWS only

...
...

Of course there is an index: (user_id, create_time). How can I make it works fast without this workaround?

This is explain analyze of slow version:

Limit  (cost=3.97..910.15 rows=10 width=146) (actual time=20047.515..60926.287 rows=10 loops=1)
  ->  Merge Append  (cost=3.97..13607492.42 rows=150164 width=146) (actual time=20047.511..60926.275 rows=10 loops=1)
        Sort Key: l.create_time DESC
        ->  Index Scan Backward using logs_2022_08_create_time_idx on logs_2022_08 l_1  (cost=0.14..13.17 rows=1 width=150) (actual time=0.071..0.072 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 33
        ->  Index Scan Backward using logs_2022_09_create_time_idx on logs_2022_09 l_2  (cost=0.14..331.27 rows=13 width=732) (actual time=0.004..0.004 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2022_10_create_time_idx on logs_2022_10 l_3  (cost=0.28..170.62 rows=13 width=129) (actual time=2.200..2.201 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 2539
        ->  Index Scan Backward using logs_2022_11_create_time_idx on logs_2022_11 l_4  (cost=0.14..331.27 rows=13 width=732) (actual time=0.005..0.005 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2022_12_create_time_idx on logs_2022_12 l_5  (cost=0.14..331.27 rows=13 width=732) (actual time=0.006..0.006 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2023_01_create_time_idx on logs_2023_01 l_6  (cost=0.14..331.27 rows=13 width=732) (actual time=0.004..0.004 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2023_02_create_time_idx on logs_2023_02 l_7  (cost=0.14..331.27 rows=13 width=732) (actual time=0.006..0.006 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2023_03_create_time_idx on logs_2023_03 l_8  (cost=0.14..331.27 rows=13 width=732) (actual time=0.006..0.006 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2023_04_create_time_idx on logs_2023_04 l_9  (cost=0.14..331.27 rows=13 width=732) (actual time=0.018..0.018 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
        ->  Index Scan Backward using logs_2023_05_create_time_idx on logs_2023_05 l_10  (cost=0.42..57850.64 rows=1228 width=136) (actual time=369.628..369.628 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 911143
        ->  Index Scan Backward using logs_2023_06_create_time_idx on logs_2023_06 l_11  (cost=0.43..174034.93 rows=2227 width=136) (actual time=1041.777..1041.778 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 2741483
        ->  Index Scan Backward using logs_2023_07_create_time_idx on logs_2023_07 l_12  (cost=0.43..193774.34 rows=7559 width=136) (actual time=1142.189..1142.189 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 3052669
        ->  Index Scan Backward using logs_2023_08_create_time_idx on logs_2023_08 l_13  (cost=0.43..277219.15 rows=10433 width=128) (actual time=1633.194..1633.194 rows=0 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 4435031
        ->  Index Scan Backward using logs_default_create_time_idx on logs_default l_14  (cost=0.57..12898500.97 rows=128612 width=148) (actual time=15858.387..56737.138 rows=10 loops=1)
              Filter: (user_id = ANY ('{1,2,3,4,5}'::integer[]))
              Rows Removed by Filter: 195041011
Planning Time: 2.091 ms
Execution Time: 60926.450 ms
Tags:

2

Answers


  1. 0

    A better solution is to never use things that are iterative… Including IN…

    So try :

    CREATE TEMPORARY TABLE T (I INT PRIMARY KEY);

INSERT INTO T VALUES (1), (2), (3), (4), (5);

SELECT      *
FROM        logs AS l
            JOIN T ON l.user_id = T.I
ORDER BY    create_time DESC
OFFSET      0 ROWS 
FETCH FIRST 10 ROWS only
    Login or Signup to reply.
  2. 0

    Is it faster when you create a second index? (Create_time, userid)? Do you use a clustered index?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search