2

I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for every column and takes around 15 seconds to complete. Here are the results when running the query using EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT id, entity_id, request_json FROM db_log dl WHERE status = 'FAILED' AND db_type = 'SOME_VALUE' ORDER BY id DESC LIMIT 1;

                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..1323.96 rows=1 width=673) (actual time=16343.062..16343.062 rows=0 loops=1)
   ->  Index Scan Backward using db_log_pkey on db_log dl  (cost=0.43..1915136.79 rows=1447 width=673) (actual time=16343.061..16343.061 rows=0 loops=1)
         Filter: (((status)::text = 'FAILED'::text) AND ((db_type)::text = 'SSM_SEND_XML_SKRIT'::text))
         Rows Removed by Filter: 9471684
 Planning Time: 0.429 ms
 Execution Time: 16343.079 ms
(6 rows)

I have already created an index using the following query:

CREATE INDEX idx_combine_status_dbtype_id ON public.db_log USING btree (status, db_type, id);

DDL for that table:

CREATE TABLE public.db_log (
    id bigserial NOT NULL,
    db_type varchar(30) NULL DEFAULT 'NULL::character varying'::character varying,
    entity_id varchar(200) NULL DEFAULT NULL::character varying,
    request_json text NULL,
    response_json text NULL,
    time_submitted timestamp NULL,
    time_request timestamp NULL,
    time_response timestamp NULL,
    created_by int4 NULL,
    created_date timestamp NULL,
    updated_by int4 NULL,
    updated_date timestamp NULL,
    status varchar(20) NULL DEFAULT 'NULL::character varying'::character varying,
    url_db varchar(200) NULL,
    CONSTRAINT db_log_pkey PRIMARY KEY (id)
);
CREATE INDEX combine_status_dbtype ON public.db_log USING btree (status, db_type);
CREATE INDEX combine_dbtype_entityid ON public.db_log USING btree (db_type, entity_id);
CREATE INDEX idx_combine_status_dbtype_id ON public.db_log USING btree (status, db_type, id);
CREATE INDEX idx_db_log_entity_id ON public.db_log USING btree (entity_id);
CREATE INDEX idx_db_log_status ON public.db_log USING btree (status);
CREATE INDEX idx_db_log_time_request ON public.db_log USING btree (time_request);
CREATE INDEX idx_db_log_time_response ON public.db_log USING btree (time_response);
CREATE INDEX idx_db_log_time_submitted ON public.db_log USING btree (time_submitted);
CREATE INDEX idx_db_log_db_type ON public.db_log USING btree (db_type);

What is the appropriate solution to address this issue?

4
  • Please consider instructions for performance questions: dba.meta.stackexchange.com/a/3299/3684 Always start by declaring your version of Postgres. Also, please reconsider the title, as your query plan clearly shows an index scan. May 20, 2023 at 2:02
  • hi @ErwinBrandstetter, thank you for your response. I have already edited the question and the title. You are correct that the query is using an index scan, but only for the "id" column, not for the "status" and "ws_type" columns. I believe this is causing the query to run slowly.
    – Rio Fajar
    May 20, 2023 at 2:29
  • The current minor version is 13.11. "We always recommend that all users run the latest available minor release for whatever major version is in use. " See: postgresql.org/support/versioning (Of course, Postgres 15 would be even better ...) May 20, 2023 at 2:49
  • It should be using your index idx_combine_status_dbtype_id. It is hard to see why it isn't using it. Maybe it would help to see the plan where it is forced to use that index. I think the only way to force that is to drop the primary key. You could use a nonproduction clone to do that, or you could start a transaction, drop the primary key, do the EXPLAIN, then rollback the drop.
    – jjanes
    May 21, 2023 at 1:26

2 Answers 2

3

Basic problem

Postgres walks the index (backwards) on the primary key column (id) expecting to find one (!) qualifying row soon enough. Turns out, it has to skip close to 10 million rows until it finally does:

Rows Removed by Filter: 9471684

Basic solutions

There are various ways to improve performance. Maybe column statistics are just outdated and a plain

ANALYZE db_log;

fixes the problem. You may want more aggressive autovacuum settings (for this table only) to keep it that way. Maybe increasing the statistics target for the filter columns helps:

ALTER TABLE db_log
  ALTER COLUMN status SET STATISTICS 2000  -- or similar
, ALTER COLUMN db_type SET STATISTICS 2000;  -- or similar

(And then run ANALYZE.)

Maybe (also) some cost settings are inadequate ...

Basically, the index you created on (status, db_type, id) is applicable to your query. status and db_type are varchar(20) and varchar(30) (you might have that cheaper to begin with ...) So the index is much bigger than the one (id). So if Postgres expects to find a qualifying row soon enough it may still choose the plan we see.

Suspected specific problem

I suspect the core problem is this: You have some types of databases (db_type) that fail often, and others rarely do. Postgres by default only gathers per-column statistics. It finds that status = 'FAILED' is rather common overall, and it finds that db_type = 'SOME_VALUE' is also rather common. It combines the selectivity of both predicates and reckons walking the index on (id) - which meets your ORDER BY - will find a qualifying row soon enough to be the best plan.

What Postgres does not realize is that the combination is not common at all. The given type of DB actually never (or hardly ever) fails. So Postgres ends up walking (almost) the whole index, which turns out to be a worst-case plan.

Solution: "multivariate statistics"

Postgres 10 added "multivariate statistics". In particular, you need multivariate MCV lists:

CREATE STATISTICS db_log_status_db_type (mcv)
ON status, db_type FROM db_log;

The next ANALYZE will gather the most common combination of status and db_type (and their frequency). This should enable Postgres to realize that it won't find a matching row for the given combination any time soon, and prefer a different query plan.

Partial index

If you run this kind of query often, and one or more of the filters are selective and immutable, consider a partial index like:

CREATE INDEX ON db_log (id) WHERE status = 'FAILED';

For a much smaller index, less maintenance cost, and faster query time.

Brute force

If all else fails (which should not be the case), one brute force method is to "disable" the index on (id) by obfuscating the ORDER BY:

ORDER BY id + 0 DESC

The no-op id + 0 hides from Postgres that the index on (id) would be applicable, and it will take the next best option ...

Related:

6
  • One odd detail, though: The query plan reports ws_log_pkey, but your table def says db_log_pkey. Also: ws_type vs. db_type. Are you sure the query targets public.db_log, and not some_other_schema.db_log? May 20, 2023 at 2:51
  • 1
    the final solution, which adding a brute force method to "disable" the index on (id) by obfuscating the ORDER BY, solves the problem. Now the query runs in less than 1 ms, and yes, I will consider using a partial index in the future if it is possible. Thank you very much for your help!
    – Rio Fajar
    May 20, 2023 at 3:05
  • about that, the actual table name is db_log and for the column is db_type but i modifying it, i will edit my question
    – Rio Fajar
    May 20, 2023 at 3:07
  • @RioFajar: My "brute force" solution is the easy way out. If there is an underlying problem you should (also) find and fix it ... May 20, 2023 at 3:09
  • yes off course, but right now i thinks it's the best way, because i have been increasing the statistics and analyze the table several times but the problem still exists.
    – Rio Fajar
    May 20, 2023 at 3:13
1

When you have INDEX(a,b), you don't also need INDEX(a). So get rid of some of your indexes. This may let the Optimizer pick the 'best' index.

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.