Need help optimizing a slow Postgres query

Question

It feels like we have all appropriate indexes created, but something feels off that it takes so long. It runs a sequential scan on snippets despite there being indexes on both fields it's filter on.

There's 4.7M records in snippets and 5.1M records in abuse_types_snippets.

SELECT count(1)
    FROM snippets
    WHERE snippets.id IN (
        SELECT abuse_types_snippets.snippet_id
        FROM abuse_types_snippets
        WHERE abuse_types_snippets.abuse_type_id = 1047122679
    )
    AND snippets.status IN (3,4,5) 
    AND snippets.created_at BETWEEN '2018-01-01' AND '2018-08-22'

Aggregate  (cost=1053185.69..1053185.70 rows=1 width=0) (actual time=52116.440..52116.441 rows=1 loops=1)
  ->  Hash Join  (cost=40922.22..1052044.88 rows=456325 width=0) (actual time=3779.600..51979.138 rows=500147 loops=1)
        Hash Cond: (snippets.id = abuse_types_snippets.snippet_id)
        ->  Seq Scan on snippets  (cost=0.00..948983.91 rows=3321771 width=4) (actual time=0.009..45231.634 rows=3277699 loops=1)
              Filter: ((created_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2018-08-22 00:00:00'::timestamp without time zone) AND (status = ANY ('{3,4,5}'::integer[])))
              Rows Removed by Filter: 1502893
        ->  Hash  (cost=30179.02..30179.02 rows=654816 width=4) (actual time=3778.402..3778.402 rows=668040 loops=1)
              Buckets: 16384  Batches: 8  Memory Usage: 3283kB
              ->  Index Only Scan using index_abuse_types_snippets_on_abuse_type_id_and_snippet_id on abuse_types_snippets  (cost=0.43..30179.02 rows=654816 width=4) (actual time=0.916..3457.052 rows=668040 loops=1)
                    Index Cond: (abuse_type_id = 1047122656)
                    Heap Fetches: 398150
Planning time: 0.319 ms
Execution time: 52117.034 ms

Added, alternative approach (join instead of sub-select, no better performance):

EXPLAIN ANALYZE SELECT count(1)
    FROM snippets
    join abuse_types_snippets on abuse_types_snippets.snippet_id = snippets.id
    WHERE abuse_types_snippets.abuse_type_id = 1047122656
    AND snippets.status IN (3,4,5) 
    AND snippets.created_at BETWEEN '2018-01-01' AND '2018-08-22'

Aggregate  (cost=1061553.74..1061553.75 rows=1 width=0) (actual time=53597.015..53597.015 rows=1 loops=1)
  ->  Hash Join  (cost=1003482.48..1060412.92 rows=456325 width=0) (actual time=49697.488..53445.218 rows=500147 loops=1)
        Hash Cond: (abuse_types_snippets.snippet_id = snippets.id)
        ->  Index Only Scan using index_abuse_types_snippets_on_abuse_type_id_and_snippet_id on abuse_types_snippets  (cost=0.43..30183.02 rows=654816 width=4) (actual time=0.977..3060.787 rows=668108 loops=1)
              Index Cond: (abuse_type_id = 1047122656)
              Heap Fetches: 396932
        ->  Hash  (cost=948983.91..948983.91 rows=3321771 width=4) (actual time=49233.187..49233.187 rows=3277699 loops=1)
              Buckets: 16384  Batches: 64  Memory Usage: 1819kB
              ->  Seq Scan on snippets  (cost=0.00..948983.91 rows=3321771 width=4) (actual time=0.010..47723.895 rows=3277699 loops=1)
                    Filter: ((created_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2018-08-22 00:00:00'::timestamp without time zone) AND (status = ANY ('{3,4,5}'::integer[])))
                    Rows Removed by Filter: 1503495
Planning time: 0.324 ms
Execution time: 53597.528 ms

Table schema:

CREATE TABLE "public"."snippets" (
    "id" int4 NOT NULL DEFAULT nextval('snippets_id_seq'::regclass),
    "status" int4 DEFAULT 0,
    "provider" varchar NOT NULL,
    "score" jsonb DEFAULT '{}'::jsonb,
    "connection_id" int4,
    "child_id" int4,
    "flagged" bool DEFAULT false,
    "created_at" timestamp(6) NOT NULL,
    "updated_at" timestamp(6) NOT NULL,
    "posted_at" timestamp(6) NOT NULL,
    "other_id" text,
    "other_name" text,
    "content_hash" varchar,
    "locked_at" timestamp(6) NULL,
    "activity_ids_for_dynamodb" varchar[] DEFAULT '{}'::character varying[],
    "filter_media" bool DEFAULT false,
    "severity" float8 DEFAULT 1.0,
    "migrated" bool DEFAULT false,
    "notified_authorities_at" timestamp(6) NULL,
    CONSTRAINT "snippets_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "fk_rails_2c8d6dd3ab" FOREIGN KEY ("connection_id") REFERENCES "public"."connections" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "fk_rails_bf28d24ffc" FOREIGN KEY ("child_id") REFERENCES "public"."children" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."snippets" OWNER TO "bark";
CREATE INDEX "index_snippets_on_child_id" ON "public"."snippets" USING btree(child_id ASC NULLS LAST);
CREATE INDEX "index_snippets_on_child_id_and_status" ON "public"."snippets" USING btree(child_id ASC NULLS LAST, status ASC NULLS LAST);
CREATE INDEX "index_snippets_on_connection_id" ON "public"."snippets" USING btree(connection_id ASC NULLS LAST);
CREATE INDEX "index_snippets_on_content_hash" ON "public"."snippets" USING btree(content_hash ASC NULLS LAST);
CREATE INDEX "index_snippets_on_created_at" ON "public"."snippets" USING btree(created_at ASC NULLS LAST);
CREATE INDEX "index_snippets_on_created_at_and_status" ON "public"."snippets" USING btree(created_at ASC NULLS LAST, status ASC NULLS LAST);
CREATE INDEX "index_snippets_on_locked_at" ON "public"."snippets" USING btree(locked_at ASC NULLS LAST);
CREATE INDEX "index_snippets_on_posted_at" ON "public"."snippets" USING btree(posted_at ASC NULLS LAST);
CREATE INDEX "index_snippets_on_status" ON "public"."snippets" USING btree(status ASC NULLS LAST);

CREATE TABLE "public"."abuse_types_snippets" (
    "abuse_type_id" int4 NOT NULL,
    "snippet_id" int4 NOT NULL,
    CONSTRAINT "fk_rails_4e19f07aa3" FOREIGN KEY ("abuse_type_id") REFERENCES "public"."abuse_types" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "fk_rails_c0ca4cc2fc" FOREIGN KEY ("snippet_id") REFERENCES "public"."snippets" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."abuse_types_snippets" OWNER TO "bark";
CREATE INDEX "index_abuse_types_snippets_on_abuse_type_id" ON "public"."abuse_types_snippets" USING btree(abuse_type_id ASC NULLS LAST);
CREATE UNIQUE INDEX "index_abuse_types_snippets_on_abuse_type_id_and_snippet_id" ON "public"."abuse_types_snippets" USING btree(abuse_type_id ASC NULLS LAST, snippet_id ASC NULLS LAST);
CREATE INDEX "index_abuse_types_snippets_on_snippet_id" ON "public"."abuse_types_snippets" USING btree(snippet_id ASC NULLS LAST);

I would add the "id" column to "index_snippets_on_created_at_and_status" , then Postgres could scan that index iso the full table. That may be a lot faster. BTW, are you sure you need all the indexes you created? — Gerard H. Pille, Aug 28 '18 at 7:13
Indexes index_snippets_on_child_id and index_snippets_on_created_at are a waste of time and space, since there are other indexes starting with those columns. — Gerard H. Pille, Aug 28 '18 at 7:16
Postgres just doesn't like to use an index on timestamps. When I replaced that column by a bigint containing datetime as a unix epoch, there were no such problems. I'll document it when it's daylight. — Gerard H. Pille, Aug 28 '18 at 23:55

Jimmy Stenke · Accepted Answer · 2018-08-28 04:55:49Z

As you say, the issue here is the sequential scan that runs despite of indexes. This happens when the query planner deems there is not enough to be gained by using an index.

It should be using the index index_snippets_on_created_at_and_status when selecting the data, but it doesn't, most likely due to the wrong cardinality. In short, you need to switch places on status and created_at

In case you don't know how a btree index works, here is a (bad) attempt to explain it.

the index is laid out like a tree, with the first column as the top leaf (root/stem), and the second column as child leafs (or branches) to that leaf. This means that when going through the index, it looks up the first column, and then picks out the child that matches the second column.

Assuming that you have a timestamp in the created_at column, and say 5 different statuses, there are 864000 possible positions in the top row per day that it needs to look up, and then filter out those rows that do not match the status. This would incur a heavy cost, especially since you're including most of the table already in the selection.

On the other hand, if status would be first, it would only have to look up 3 positions in the index, and then do a range scan on the second column, which would have a lower cost.

So, changing the positions might help, but it is of course no guarantee. Since you are selecting 3.2M rows out of 4.7M, the planner might chose not to use the index anyway since the main cost might be the actual fetching of the rows.

If that happens, you could try to include id in the index as well (as the last column) since then the planner might be able to use that to filter from abuse_types_snippets without looking up the rows until the last minute.

Thanks for the insight. Sadly, neither had any effect on the query, but I appreciate you taking the time! — brandonhilkert, Aug 28 '18 at 12:01
Ok, then it's most probably because you select 70% of the table, so the planner doesn't see any benefits of using an index. You could however try with an analyze snippets and see if there is a change. — Jimmy Stenke, Aug 28 '18 at 12:16

Stack Exchange Network

current community

your communities

more stack exchange communities

Need help optimizing a slow Postgres query

1 Answer 1

Your Answer

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

Hot Network Questions

Need help optimizing a slow Postgres query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions