Slow query execution with particular conditions

Question

I have Users table:

CREATE TABLE public.users (
    id integer NOT NULL,
    first_name character varying,
    last_name character varying,
    nickname character varying,
    privacy integer
);

With following index:

CREATE INDEX index_users_on_privacy
    ON public.users USING btree
    (privacy)
    TABLESPACE pg_default;

When I run the following query, I got the expected results with proper execution time:

SELECT  "users".* FROM "users" 
WHERE "users"."id" < 20000
ORDER BY "users"."id" DESC LIMIT 4

Explanation:

"Limit  (cost=541524.58..541524.59 rows=4 width=1509) (actual time=88.974..89.021 rows=4 loops=1)"
"  ->  Sort  (cost=541524.58..542109.51 rows=233972 width=1509) (actual time=88.964..88.978 rows=4 loops=1)"
"        Sort Key: id DESC"
"        Sort Method: top-N heapsort  Memory: 37kB"
"        ->  Bitmap Heap Scan on users  (cost=3445.58..538015.00 rows=233972 width=1509) (actual time=4.515..50.689 rows=7012 loops=1)"
"              Recheck Cond: (id < 20000)"
"              Heap Blocks: exact=4973"
"              ->  Bitmap Index Scan on users_pkey  (cost=0.00..3387.09 rows=233972 width=0) (actual time=3.735..3.735 rows=7012 loops=1)"
"                    Index Cond: (id < 20000)"
"Planning time: 0.263 ms"
"Execution time: 89.707 ms"

Now when I try to add any filter to where clause, (i.e. apply like on first_name or last_name or nickname), I will get perfect performance as well, but by adding the following specific condition

AND "users"."privacy" = 0

I'm getting extremely slow execution time

Query:

SELECT  "users".* FROM "users" 
WHERE "users"."id" < 20000
AND "users"."privacy" = 0
ORDER BY "users"."id" DESC LIMIT 4

Explanation:

"Limit  (cost=389636.94..389636.95 rows=4 width=1509) (actual time=46687.391..46687.441 rows=4 loops=1)"
"  ->  Sort  (cost=389636.94..389958.31 rows=128547 width=1509) (actual time=46687.378..46687.394 rows=4 loops=1)"
"        Sort Key: created_at DESC"
"        Sort Method: top-N heapsort  Memory: 36kB"
"        ->  Bitmap Heap Scan on users  (cost=36688.66..387708.74 rows=128547 width=1509) (actual time=1559.659..46665.366 rows=3459 loops=1)"
"              Recheck Cond: (privacy = 0)"
"              Rows Removed by Index Recheck: 2416"
"              Filter: (id < 20000)"
"              Heap Blocks: exact=356084 lossy=527637"
"              ->  Bitmap Index Scan on index_users_on_privacy  (cost=0.00..36656.52 rows=128547 width=0) (actual time=1426.792..1426.792 rows=2706758 loops=1)"
"                    Index Cond: (privacy = 0)"
"Planning time: 150.160 ms"
"Execution time: 46780.021 ms"

Please help me to understand why I'm getting around 46 seconds difference and how to avoid it.

Notes:

I have this app live since 3 years with no issues, recently I started getting this performance issue.
PostgreSQL version is 10.3, and I tried the same long execution query on different machines, but it works properly without any issue.

user_6464308 · Answer

PostgreSQL's statistics seem to be way of, because it underestimates the number of result rows from the bitmap index scan in the second query, while it oberestimates the number of results in the first query.

That is how it arrives at the mistaken conclusion that using the index on privacy will be the most efficient strategy.

Try calculating table statistics with

ANALYZE users;

If that does the trick, configure autovacuum so that it analyzes that table more often.

If that alone is not enough, try to increase the granularity of the statistics:

ALTER TABLE users ALTER privacy SET STATISTICS 1000;
ANALYZE users;

These measures should get PostgreSQL to choose the correct plan, so that is what I recommend.

If you want to force PostgreSQL not to use that index (forcing should always be the last thing to consider), rewrite the query like this:

SELECT users.* FROM users
WHERE users.id < 20000
  AND users.privacy + 0 = 0
ORDER BY users.id DESC
LIMIT 4;

Slow query execution with particular conditions

1 Answers