Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tlcj2-000eSw-FT for pgsql-performance@arkaria.postgresql.org; Fri, 21 Feb 2025 23:46:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tlcj0-0012ub-Qr for pgsql-performance@arkaria.postgresql.org; Fri, 21 Feb 2025 23:46:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tlcj0-0012uR-65 for pgsql-performance@lists.postgresql.org; Fri, 21 Feb 2025 23:46:30 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tlciw-0002zy-1e for pgsql-performance@lists.postgresql.org; Fri, 21 Feb 2025 23:46:29 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-5fc13355a5bso1204396eaf.3 for ; Fri, 21 Feb 2025 15:46:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740181587; x=1740786387; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=EEyS5Ve4bu/CCAksbI+eS48Vav633INsMJt2LUQUUUk=; b=bSMAXdHt1YqPKW93GgpZIYnW7Tu5jBQhHNBzrsCTQTyPauX+ZLt9/e4lI5wTLvP6tQ +n6AlgIiylE6vLfAfudA08wybl0NUUM+AywIQ+Fzge63Knzszz3VAhaovNIwmUb86KUf QzBwUI7hfcgBt0CDct5wCug+R+azsORihaw0lyRyHvy1R91fobT+p+rgdnRv9qmYHC9E 4Roc1jhPtqvJtMOYgTCBou+jdQXMVH5sPO9a60/1HLTocprP896XToU+vvi89n3ZU10H BYOiJixpy+tb4DffbYvI/JxHaf/mRMCIJdeY0Eu0XX/kOrvNUTs8hjCrJSkv4Y/2abfG 640A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740181587; x=1740786387; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=EEyS5Ve4bu/CCAksbI+eS48Vav633INsMJt2LUQUUUk=; b=mFi+oeANjJcrc0/ebluk33jImk5oZYMfv1QxsHHW97Dt90p8yepaPqcNz75L2OnR+y CZaqbXTmuvdcQsoyTRog48cCCDXhd9vVmYoJcF6n3k+LeD808DFQ/Aiy70oXZfSYK0D9 ykmqR882wGcRrdxKfECEpx9YKMKtM4Ia2RIx7kvjfbPG8V0htOCLbQW0LctePd8Z+y57 nMppHJGQC8vvo54cU6ku08TfhWbK6Ue3jJxrxKvhNv96aNGoIj2dPA1ft9CLdRNFS1XV rxGoSeXH3xwMjNPeTDNdj+dHLoaRo+uB/i6MeDhWbBYjLAkYsZk0XCfzfUXO6R3acWcr 2l7g== X-Gm-Message-State: AOJu0Ywdmf8Ai/DMe3Lk2oGWSqrqqnvWeN4h4sb5mn3o+uw/asuNz/zz 9ylwM3k1x+MDfTZF0gK4WjgiPNcR1QT0Bsb5Ffy+IVYXPKUEr7NCD3zLAA2fMRgJZ9LXvo8icaX sUK7fehZYPLkoCcLaBu+AOWEZzJ5v2iP6AQ== X-Gm-Gg: ASbGnctbKxaOpbxBNacuBU8Ee9KcSJKQ2DokxeJlWaOIUP8SQt0/rJOeJLheX4+rGQi Gb1/hJzRCp6bEkc99wyzS+hKyxKPB/Njrc/DzZ5h7yvvOS1PCZZcyQUIyR9l7KYB09otqpvSmhh Uxh+55Vo4= X-Google-Smtp-Source: AGHT+IHmRphOk/78g1ZtdycPdSTI8iYisk0ef4A99iMC9qDl6HOLO6S2UifWwKvAqP6Gj+HMs76XaAxX0/yzPVDfIBc= X-Received: by 2002:a05:6808:18a7:b0:3f4:1a7d:959a with SMTP id 5614622812f47-3f424697de8mr4317714b6e.2.1740181586655; Fri, 21 Feb 2025 15:46:26 -0800 (PST) MIME-Version: 1.0 From: Lincoln Swaine-Moore Date: Fri, 21 Feb 2025 15:46:15 -0800 X-Gm-Features: AWEUYZk0G5JtmhSVP7n0cJMy87EX0UyfpSkn5sVIZPeXmq3woInOH7jzild0qOU Message-ID: Subject: Unfortunate Nested Loop + Missing Autovacuum To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000514438062eaf991e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000514438062eaf991e Content-Type: text/plain; charset="UTF-8" Hi all-- Writing in with a two-parter performance issue. I've got two tables roughly shaped like this: Table "public.rawdata" Column | Type | Collation | Nullable | Default -----------------------+-----------------------------+-----------+----------+------------------------------------------------- id | integer | | not null | nextval('rawdata_id_seq'::regclass) timestamp | timestamp without time zone | | not null | sn | character varying(36) | | not null | raw_value | double precision | | | Indexes: "rawdata_pkey" PRIMARY KEY, btree (id) "rawdata_multicol_sn_and_timestamp_desc_idx" btree (sn, "timestamp" DESC) Foreign-key constraints: "rawdata_sn_fkey" FOREIGN KEY (sn) REFERENCES devices(sn) ON UPDATE CASCADE ON DELETE CASCADE Referenced by: TABLE "cleandata" CONSTRAINT "cleandata_raw_table_id_fkey" FOREIGN KEY (raw_table_id) REFERENCES rawdata(id) Table "public.cleandata" Column | Type | Collation | Nullable | Default -----------------+-----------------------------+-----------+----------+--------------------------------------------------- id | integer | | not null | nextval('cleandata_id_seq'::regclass) timestamp | timestamp without time zone | | not null | sn | character varying(36) | | not null | raw_table_id | integer | | | clean_value | double precision | | | Indexes: "cleandata_pkey" PRIMARY KEY, btree (id) "cleandata_multicol_sn_and_timestamp_desc_idx" btree (sn, "timestamp" DESC) Foreign-key constraints: "cleandata_raw_table_id_fkey" FOREIGN KEY (raw_table_id) REFERENCES rawdata(id) "cleandata_sn_fkey" FOREIGN KEY (sn) REFERENCES devices(sn) ON UPDATE CASCADE ON DELETE CASCADE Rows are inserted into the raw table, then slightly later inserted into the cleandata table, at a rate of ~1-5 million rows a day. The tables are on the order of hundreds of millions of rows. They are almost never updated or deleted, but are selected regularly. Technically, not every row in rawdata will have a corresponding row in cleandata, but in practice the vast majority do. Thanks to the combined index on sn / timestamp, selecting data from one or the other of these tables is fairly performant. Where things get hairy is when I try to join between them. I can write a performant query that joins from clean to raw, but the opposite direction (which is not logically equivalent) gets hairy because raw_table_id is not indexed on cleandata. I think in the medium term, it would be good to build that index, but in the short term, I can help speed things up by limiting the data by doubly filtering like: explain analyze SELECT count(*) FROM ( SELECT * FROM rawdata WHERE timestamp >= NOW() - interval '2 weeks' AND sn ='FOO' ) r JOIN ( SELECT * FROM cleandata WHERE timestamp >= NOW() - interval '2 weeks' AND sn ='FOO' ) c ON r.id = c.raw_table_id; which produces: Hash Join (cost=42.80..83.52 rows=1 width=44) (actual time=852.341..1338.713 rows=20141 loops=1) Hash Cond: (cleandata.raw_table_id = rawdata.id) -> Index Scan using cleandata_multicol_sn_and_timestamp_desc_idx on cleandata (cost=0.70..41.30 rows=47 width=12) (actual time=0.051..476.287 rows=20141 loops=1) " Index Cond: (((sn)::text = 'FOO'::text) AND (""timestamp"" >= (now() - '14 days'::interval)))" -> Hash (cost=41.51..41.51 rows=47 width=40) (actual time=852.273..852.275 rows=20141 loops=1) Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1751kB -> Index Scan using raw_multicol_sn_and_timestamp_desc_idx on rawdata (cost=0.70..41.51 rows=47 width=40) (actual time=0.050..844.555 rows=20141 loops=1) " Index Cond: (((sn)::text = 'FOO'::text) AND (""timestamp"" >= (now() - '14 days'::interval)))" Planning Time: 2.211 ms Execution Time: 1339.680 ms The estimates are totally wrong, but the plan is good and the query is fast. However, when I change the query slightly, and make it `timestamp >= NOW() - interval '2 weeks' and timestamp < NOW()`, which actually covers the same period (or really any other time period), the plan changes dramatically: Nested Loop (cost=1.42..5.47 rows=1 width=572) (actual time=0.100..185218.340 rows=20141 loops=1) Join Filter: (rawdata.id = cleandata.raw_table_id) Rows Removed by Join Filter: 405639740 -> Index Scan using raw_multicol_sn_and_timestamp_desc_idx on rawdata (cost=0.71..2.73 rows=1 width=424) (actual time=0.056..100.014 rows=20141 loops=1) " Index Cond: (((sn)::text = 'FOO'::text) AND (""timestamp"" >= (now() - '14 days'::interval)) AND (""timestamp"" < now()))" -> Index Scan using cleandata_multicol_sn_and_timestamp_desc_idx on cleandata (cost=0.71..2.73 rows=1 width=148) (actual time=0.045..7.584 rows=20141 loops=20141) " Index Cond: (((sn)::text = 'FOO'::text) AND (""timestamp"" >= (now() - '14 days'::interval)) AND (""timestamp"" < now()))" Planning Time: 0.450 ms Execution Time: 185225.028 ms The row estimate for each table gets even worse, and I guess this is enough to encourage postgres to do this as a Nested Loop, with disastrous consequences. So, obviously there's a statistics problem, which led me to realize that actually these tables have *never* been autovacuumed/analyzed according to pg_stat_user_tables. I'm using a managed database which makes it a little tricky to debug, but all my settings (autovacuum/autovacuum_vacuum_threshold/autovacuum_analyze_threshold/autovacuum_vacuum_insert_threshold) are default, and I can see that other tables have been vacuumed recently. I assume this has something to do with the fact that these tables don't accumulate dead tuples since they're basically append-only? But I still think INSERTs should trigger autovacuum/analyze eventually (at least when the table grows by 10%, because of autovacuum_analyze_scale_factor), and I'm confused why that doesn't seem to have happened. Seems like this is probably hurting my queries' performance elsewhere. So, my two big questions are: - Is there a better way to write my query to hint away from the awful nested loop join? - Can anyone think of why autovacuum is declining to vacuum/analyze these tables? Thanks for reading, and for any help with this! -- Lincoln Swaine-Moore --000000000000514438062eaf991e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi all--

Writing in with a two-parter performa= nce issue.

I've got two tables roughly shaped like this:

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "public.rawdata"
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 Column =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | Collation | Nullable | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Default
-----------------------+---------------= --------------+-----------+----------+-------------------------------------= ------------
=C2=A0id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | = nextval('rawdata_id_seq'::regclass)
=C2=A0timestamp =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | timestamp without time zone | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0sn =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| character varying(36) =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0r= aw_value =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | double precision =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0Indexes:
=C2=A0 =C2=A0 &q= uot;rawdata_pkey" PRIMARY KEY, btree (id)
=C2=A0 =C2=A0 "rawda= ta_multicol_sn_and_timestamp_desc_idx" btree (sn, "timestamp"= ; DESC)
Foreign-key constraints:
=C2=A0 =C2=A0 "rawdata_sn_fkey&= quot; FOREIGN KEY (sn) REFERENCES devices(sn) ON UPDATE CASCADE ON DELETE C= ASCADE
Referenced by:
=C2=A0 =C2=A0 TABLE "cleandata" CONST= RAINT "cleandata_raw_table_id_fkey" FOREIGN KEY (raw_table_id) RE= FERENCES rawdata(id)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "public.cleandata"
= =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collation= | Nullable | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Default
-----------------+-----------------------------= +-----------+----------+---------------------------------------------------=
=C2=A0id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| integer =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | nextval('cleandata_id_seq'= ;::regclass)
=C2=A0timestamp =C2=A0 =C2=A0 =C2=A0 | timestamp without ti= me zone | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0sn =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| character varying(36) =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2=A0r= aw_table_id =C2=A0 =C2=A0| integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0clean_value =C2=A0 =C2=A0 | doubl= e precision =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
Indexes:
=C2=A0 = =C2=A0 "cleandata_pkey" PRIMARY KEY, btree (id)
=C2=A0 =C2=A0 = "cleandata_multicol_sn_and_timestamp_desc_idx" btree (sn, "t= imestamp" DESC)
Foreign-key constraints:
=C2=A0 =C2=A0 "cle= andata_raw_table_id_fkey" FOREIGN KEY (raw_table_id) REFERENCES rawdat= a(id)
=C2=A0 =C2=A0 "cleandata_sn_fkey" FOREIGN KEY (sn) REFER= ENCES devices(sn) ON UPDATE CASCADE ON DELETE CASCADE

Rows ar= e inserted into the raw table, then slightly later inserted into the cleand= ata table, at a rate of ~1-5 million
rows a day. The tables are on the o= rder of hundreds of millions of rows. They are almost never updated or dele= ted,
but are selected regularly. Technically, not every row in rawdata w= ill have a corresponding row in cleandata,
but in practice the vast majo= rity do.

Thanks to the combined index on sn / timestamp, selecting d= ata from one or the other of these tables is
fairly performant. Where th= ings get hairy is when I try to join between them. I can write a performant= query that joins
from clean to raw, but the opposite direction (which i= s not logically equivalent) gets hairy because raw_table_id is not indexed = on cleandata.
I think in the medium term, it would be good to build that= index, but in the short term, I can help speed things up
by limiting th= e data by doubly filtering like:

explain a= nalyze SELECT count(*)
FROM (
=C2=A0 =C2=A0 SELECT *
=C2=A0 =C2=A0= FROM
=C2=A0 =C2=A0 =C2=A0 =C2=A0 rawdata
=C2=A0 =C2=A0 WHERE
=C2= =A0 =C2=A0 =C2=A0 =C2=A0timestamp >=3D NOW() - interval '2 weeks'= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND sn =3D'FOO'
=C2=A0 =C2=A0 )= r
JOIN (
=C2=A0 =C2=A0 SELECT *
=C2=A0 =C2=A0 FROM
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 cleandata
=C2=A0 =C2=A0 WHERE
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 timestamp >=3D NOW() - interval '2 weeks'
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0AND sn =3D'FOO'
) c
ON
=C2=A0 = =C2=A0 r.id =3D c.raw_table_id;

w= hich produces:
Hash Join =C2=A0(cost=3D42.80..8= 3.52 rows=3D1 width=3D44) (actual time=3D852.341..1338.713 rows=3D20141 loo= ps=3D1)
=C2=A0 Hash Cond: (cleandata.raw_table_id =3D rawdata.id)
=C2=A0 -> =C2=A0Index Scan using cleandat= a_multicol_sn_and_timestamp_desc_idx on cleandata =C2=A0(cost=3D0.70..41.30= rows=3D47 width=3D12) (actual time=3D0.051..476.287 rows=3D20141 loops=3D1= )
" =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (((sn)::text =3D 'FO= O'::text) AND (""timestamp"" >=3D (now() - '= 14 days'::interval)))"
=C2=A0 -> =C2=A0Hash =C2=A0(cost=3D41= .51..41.51 rows=3D47 width=3D40) (actual time=3D852.273..852.275 rows=3D201= 41 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Buckets: 32768 (originally 102= 4) =C2=A0Batches: 1 (originally 1) =C2=A0Memory Usage: 1751kB
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -> =C2=A0Index Scan using raw_multicol_sn_and_timestam= p_desc_idx on rawdata =C2=A0(cost=3D0.70..41.51 rows=3D47 width=3D40) (actu= al time=3D0.050..844.555 rows=3D20141 loops=3D1)
" =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (((sn)::text =3D 'FOO'= ;::text) AND (""timestamp"" >=3D (now() - '14 da= ys'::interval)))"
Planning Time: 2.211 ms
Execution Time: 13= 39.680 ms

The estimates are totally wrong, but the plan is go= od and the query is fast.
However, when I change the query slightly, and= make it `timestamp >=3D NOW() - interval '2 weeks' and timestam= p < NOW()`,
which actually covers the same period (or really any othe= r time period), the plan changes dramatically:
= Nested Loop =C2=A0(cost=3D1.42..5.47 rows=3D1 width=3D572) (actual time=3D0= .100..185218.340 rows=3D20141 loops=3D1)
=C2=A0 Join Filter: (rawdata.id =3D cleandata.raw_table_id)
=C2=A0 Ro= ws Removed by Join Filter: 405639740
=C2=A0 -> =C2=A0Index Scan using= raw_multicol_sn_and_timestamp_desc_idx on rawdata =C2=A0(cost=3D0.71..2.73= rows=3D1 width=3D424) (actual time=3D0.056..100.014 rows=3D20141 loops=3D1= )
" =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (((sn)::text =3D 'FO= O'::text) AND (""timestamp"" >=3D (now() - '= 14 days'::interval)) AND (""timestamp"" < now())= )"
=C2=A0 -> =C2=A0Index Scan using cleandata_multicol_sn_and_ti= mestamp_desc_idx on cleandata =C2=A0(cost=3D0.71..2.73 rows=3D1 width=3D148= ) (actual time=3D0.045..7.584 rows=3D20141 loops=3D20141)
" =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: (((sn)::text =3D 'FOO'::text) AND (= ""timestamp"" >=3D (now() - '14 days'::inter= val)) AND (""timestamp"" < now()))"
Planning= Time: 0.450 ms
Execution Time: 185225.028 ms

The row esti= mate for each table gets even worse, and I guess this is enough to encourag= e postgres to do this as a Nested Loop, with disastrous consequences.
So, obviously there's a statistics problem, which led me to realize t= hat actually these tables have *never* been autovacuumed/analyzed according= to pg_stat_user_tables.=C2=A0
I'm using a managed database w= hich makes it a little tricky to debug, but all my settings
(autovacuum= /autovacuum_vacuum_threshold/autovacuum_analyze_threshold/autovacuum_vacuum= _insert_threshold) are default,
and I can see that other tables have bee= n vacuumed recently.

I assume this has something to do with the fact= that these tables don't accumulate dead tuples since they're basic= ally append-only?
But I still think INSERTs should trigger autovacuum/a= nalyze eventually (at least when the table grows by 10%, because of autovac= uum_analyze_scale_factor),
and I'm confused why that doesn't se= em to have happened. Seems like this is probably hurting my queries' pe= rformance elsewhere.

So, my two big questions are:
- Is there a b= etter way to write my query to hint away from the awful nested loop join?- Can anyone think of why autovacuum is declining to vacuum/analyze these= tables?

Thanks for reading, and for any help with this!
<= br>
--
Lincoln Sw= aine-Moore
--000000000000514438062eaf991e--