Thiết kế website giá rẻ

Question

I created a simple table:

CREATE TABLE logs (
    user_id int4 NOT NULL,
    create_time timestamptz NOT NULL DEFAULT now()
);

Inserted some random data:

INSERT INTO logs (user_id) 
(SELECT RANDOM() * 70 + 10 FROM generate_series(1,200000000));

Added indexes:

CREATE INDEX logs_user_id_idx ON ONLY logs USING btree (user_id);
CREATE INDEX logs_create_time_idx ON ONLY logs USING btree (create_time);
CREATE INDEX logs_user_id_create_time_idx ON ONLY logs USING btree (user_id, create_time);

Analyzed the table:

ANALYZE logs;

Now, both of theese queries:

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

EXPLAIN ANALYZE
SELECT *
FROM logs
WHERE user_id IN (1001, 1002)
ORDER BY
    create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

Uses logs_user_id_create_time_idx index, which is fast (miliseconds to finish).

Now I deleted all the rows and inserted new ones, but with manually picked data, so that a small number of users takes majority of the rows:

TRUNCATE TABLE logs;

INSERT INTO logs (user_id) (select 145 from generate_series(1,95815778));
INSERT INTO logs (user_id) (select 257 from generate_series(1,25574680));
INSERT INTO logs (user_id) (select 255 from generate_series(1,17013301));
INSERT INTO logs (user_id) (select 310 from generate_series(1,14986006));
INSERT INTO logs (user_id) (select 254 from generate_series(1,14701714));
INSERT INTO logs (user_id) (select 292 from generate_series(1,13147405));
INSERT INTO logs (user_id) (select 150 from generate_series(1,7181137));
INSERT INTO logs (user_id) (select 247 from generate_series(1,6930556));
INSERT INTO logs (user_id) (select 403 from generate_series(1,4260893));
INSERT INTO logs (user_id) (select 293 from generate_series(1,3702963));
INSERT INTO logs (user_id) (select 275 from generate_series(1,3215303));
INSERT INTO logs (user_id) (select 253 from generate_series(1,2648267));
INSERT INTO logs (user_id) (select 184 from generate_series(1,1197320));
INSERT INTO logs (user_id) (select 221 from generate_series(1,802206));
INSERT INTO logs (user_id) (select 309 from generate_series(1,709847));
INSERT INTO logs (user_id) (select 308 from generate_series(1,661653));
INSERT INTO logs (user_id) (select 260 from generate_series(1,628120));
INSERT INTO logs (user_id) (select 228 from generate_series(1,590788));
INSERT INTO logs (user_id) (select 438 from generate_series(1,433570));
INSERT INTO logs (user_id) (select 316 from generate_series(1,291468));
INSERT INTO logs (user_id) (select 160 from generate_series(1,195000));
INSERT INTO logs (user_id) (select 264 from generate_series(1,158459));
INSERT INTO logs (user_id) (select 266 from generate_series(1,55986));
INSERT INTO logs (user_id) (select 229 from generate_series(1,34213));
INSERT INTO logs (user_id) (select 419 from generate_series(1,21664));
INSERT INTO logs (user_id) (select 155 from generate_series(1,17407));
INSERT INTO logs (user_id) (select 152 from generate_series(1,9509));
INSERT INTO logs (user_id) (select 258 from generate_series(1,7979));
INSERT INTO logs (user_id) (select 149 from generate_series(1,7451));
INSERT INTO logs (user_id) (select 153 from generate_series(1,4918));
INSERT INTO logs (user_id) (select 156 from generate_series(1,4862));
INSERT INTO logs (user_id) (select 147 from generate_series(1,3155));
INSERT INTO logs (user_id) (select 157 from generate_series(1,2762));
INSERT INTO logs (user_id) (select 93 from generate_series(1,2622));
INSERT INTO logs (user_id) (select 401 from generate_series(1,2169));
INSERT INTO logs (user_id) (select 420 from generate_series(1,1816));
INSERT INTO logs (user_id) (select 273 from generate_series(1,1632));
INSERT INTO logs (user_id) (select 154 from generate_series(1,1416));
INSERT INTO logs (user_id) (select 321 from generate_series(1,1099));
INSERT INTO logs (user_id) (select 231 from generate_series(1,1055));
INSERT INTO logs (user_id) (select 233 from generate_series(1,830));
INSERT INTO logs (user_id) (select 146 from generate_series(1,612));
INSERT INTO logs (user_id) (select 318 from generate_series(1,561));
INSERT INTO logs (user_id) (select 148 from generate_series(1,548));
INSERT INTO logs (user_id) (select 334 from generate_series(1,501));
INSERT INTO logs (user_id) (select 252 from generate_series(1,493));
INSERT INTO logs (user_id) (select 182 from generate_series(1,336));
INSERT INTO logs (user_id) (select 326 from generate_series(1,248));
INSERT INTO logs (user_id) (select 151 from generate_series(1,210));
INSERT INTO logs (user_id) (select 190 from generate_series(1,163));
INSERT INTO logs (user_id) (select 392 from generate_series(1,138));
INSERT INTO logs (user_id) (select 195 from generate_series(1,114));
INSERT INTO logs (user_id) (select 194 from generate_series(1,104));
INSERT INTO logs (user_id) (select 232 from generate_series(1,102));
INSERT INTO logs (user_id) (select 1 from generate_series(1,96));
INSERT INTO logs (user_id) (select 407 from generate_series(1,94));
INSERT INTO logs (user_id) (select 193 from generate_series(1,93));
INSERT INTO logs (user_id) (select 234 from generate_series(1,86));
INSERT INTO logs (user_id) (select 327 from generate_series(1,82));
INSERT INTO logs (user_id) (select 198 from generate_series(1,77));
INSERT INTO logs (user_id) (select 187 from generate_series(1,76));
INSERT INTO logs (user_id) (select 428 from generate_series(1,75));
INSERT INTO logs (user_id) (select 439 from generate_series(1,62));
INSERT INTO logs (user_id) (select 307 from generate_series(1,44));
INSERT INTO logs (user_id) (select 186 from generate_series(1,42));
INSERT INTO logs (user_id) (select 83 from generate_series(1,41));
INSERT INTO logs (user_id) (select 222 from generate_series(1,38));
INSERT INTO logs (user_id) (select 313 from generate_series(1,38));
INSERT INTO logs (user_id) (select 270 from generate_series(1,34));
INSERT INTO logs (user_id) (select 143 from generate_series(1,32));
INSERT INTO logs (user_id) (select 75 from generate_series(1,31));
INSERT INTO logs (user_id) (select 395 from generate_series(1,28));
INSERT INTO logs (user_id) (select 250 from generate_series(1,24));
INSERT INTO logs (user_id) (select 104 from generate_series(1,24));
INSERT INTO logs (user_id) (select 180 from generate_series(1,22));
INSERT INTO logs (user_id) (select 416 from generate_series(1,19));
INSERT INTO logs (user_id) (select 220 from generate_series(1,18));
INSERT INTO logs (user_id) (select 406 from generate_series(1,16));
INSERT INTO logs (user_id) (select 249 from generate_series(1,15));
INSERT INTO logs (user_id) (select 320 from generate_series(1,15));
INSERT INTO logs (user_id) (select 394 from generate_series(1,14));
INSERT INTO logs (user_id) (select 322 from generate_series(1,14));
INSERT INTO logs (user_id) (select 383 from generate_series(1,14));
INSERT INTO logs (user_id) (select 332 from generate_series(1,14));
INSERT INTO logs (user_id) (select 248 from generate_series(1,12));
INSERT INTO logs (user_id) (select 375 from generate_series(1,10));
INSERT INTO logs (user_id) (select 28 from generate_series(1,10));
INSERT INTO logs (user_id) (select 376 from generate_series(1,10));
INSERT INTO logs (user_id) (select 333 from generate_series(1,9));
INSERT INTO logs (user_id) (select 177 from generate_series(1,8));
INSERT INTO logs (user_id) (select 319 from generate_series(1,8));
INSERT INTO logs (user_id) (select 381 from generate_series(1,7));
INSERT INTO logs (user_id) (select 207 from generate_series(1,6));
INSERT INTO logs (user_id) (select 374 from generate_series(1,6));
INSERT INTO logs (user_id) (select 197 from generate_series(1,6));
INSERT INTO logs (user_id) (select 251 from generate_series(1,5));
INSERT INTO logs (user_id) (select 176 from generate_series(1,5));
INSERT INTO logs (user_id) (select 224 from generate_series(1,4));
INSERT INTO logs (user_id) (select 166 from generate_series(1,4));
INSERT INTO logs (user_id) (select 161 from generate_series(1,4));
INSERT INTO logs (user_id) (select 317 from generate_series(1,4));
INSERT INTO logs (user_id) (select 178 from generate_series(1,4));
INSERT INTO logs (user_id) (select 399 from generate_series(1,4));
INSERT INTO logs (user_id) (select 185 from generate_series(1,4));
INSERT INTO logs (user_id) (select 191 from generate_series(1,4));
INSERT INTO logs (user_id) (select 162 from generate_series(1,4));
INSERT INTO logs (user_id) (select 386 from generate_series(1,3));
INSERT INTO logs (user_id) (select 169 from generate_series(1,3));
INSERT INTO logs (user_id) (select 328 from generate_series(1,3));
INSERT INTO logs (user_id) (select 400 from generate_series(1,3));
INSERT INTO logs (user_id) (select 201 from generate_series(1,2));
INSERT INTO logs (user_id) (select 81 from generate_series(1,2));
INSERT INTO logs (user_id) (select 223 from generate_series(1,2));
INSERT INTO logs (user_id) (select 196 from generate_series(1,2));
INSERT INTO logs (user_id) (select 389 from generate_series(1,2));
INSERT INTO logs (user_id) (select 101 from generate_series(1,2));
INSERT INTO logs (user_id) (select 94 from generate_series(1,2));
INSERT INTO logs (user_id) (select 269 from generate_series(1,1));
INSERT INTO logs (user_id) (select 385 from generate_series(1,1));
INSERT INTO logs (user_id) (select 398 from generate_series(1,1));

ANALYZE logs;

Now, this this query:

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

is still using logs_user_id_create_time_idx index, but this query:

EXPLAIN ANALYZE
SELECT *
FROM logs
WHERE user_id IN (1001, 1002)
ORDER BY
    create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

is using logs_create_time_idx, which is terribly slow (30 seconds instead miliseconds). If I remove logs_create_time_idx index, then logs_user_id_idx index will be used, which will make the query finish in miliseconds.

The engine is choosing wrong index, so my query takes too longs to finish. From what I know, in sql server I can force the engine to use a specific index. Is there something I can do in postgres?

Thiết kế website giá rẻ

Danh mục

Wrong index is being choosed when data are not random