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);