Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lik4g-0001h5-UP for pgsql-performance@arkaria.postgresql.org; Mon, 17 May 2021 20:42:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lik4f-0003eI-Ky for pgsql-performance@arkaria.postgresql.org; Mon, 17 May 2021 20:42:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lik4f-0003e4-8s for pgsql-performance@lists.postgresql.org; Mon, 17 May 2021 20:42:49 +0000 Received: from outgoing18.cpt4.host-h.net ([196.40.103.48]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lik4Z-0005Pj-AB for pgsql-performance@lists.postgresql.org; Mon, 17 May 2021 20:42:47 +0000 Received: from www20.cpt1.host-h.net ([197.221.2.20]) by antispam6-cpt4.host-h.net with esmtpsa (TLSv1.2:AES128-GCM-SHA256:128) (Exim 4.92) (envelope-from ) id 1lik4N-0001Y1-U8 for pgsql-performance@lists.postgresql.org; Mon, 17 May 2021 22:42:36 +0200 Received: from [155.93.246.199] (helo=DMZHOST) by www20.cpt1.host-h.net with esmtpa (Exim 4.89) (envelope-from ) id 1lik4L-0003mD-Iz for pgsql-performance@lists.postgresql.org; Mon, 17 May 2021 22:42:29 +0200 Received: from M6800 (Unknown [10.0.0.22]) by DMZHOST with ESMTPSA (version=TLSv1.2 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256) ; Mon, 17 May 2021 22:49:01 +0200 From: To: Subject: Index and statistics not used Date: Mon, 17 May 2021 22:42:25 +0200 Message-ID: <028e01d74b5d$25494700$6fdbd500$@exa.co.za> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_028F_01D74B6D.E8D21700" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AddLWnEGwPn4yx59R6y7jcLwFzNTbg== Content-Language: en-za X-Authenticated-Sender: smtprelay@exa.co.za X-Virus-Scanned: Clear X-Originating-IP: 197.221.2.20 X-SpamExperts-Domain: exa.co.za X-SpamExperts-Username: Authentication-Results: host-h.net; auth=pass (login) smtp.auth=@exa.co.za X-SpamExperts-Outgoing-Class: ham X-SpamExperts-Outgoing-Evidence: Combined (0.20) X-Recommended-Action: accept X-Filter-ID: Pt3MvcO5N4iKaDQ5O6lkdGlMVN6RH8bjRMzItlySaT/oGSMyu24aOSCZ+BYog0+oPUtbdvnXkggZ 3YnVId/Y5jcf0yeVQAvfjHznO7+bT5x3YlAu3sgx6moFDmjRJ9go2h2Kd7TxjNrwU1q4E4pz70zG /Ri9qsLHJr5sHmEYFjuCSzNIuWQJrNBB673AmlSDZ+nDjgQgute8UNEeahua78yF1edjsnoh92FV V/dq/ccnu3Vnse4qFhyVxtUee5mCpuGXed8VjoeeEKguXydrkQQXEBFMPIRe1deJFN9bGdmGKpfT IF8fSxNC0LttVkKWSrXh5xkkUIHG7SAJficvImvTQr9LsoRJmgY0zriN/5RNxaypTbp28NxghjNo pU/Oaqj4KISIRSJ5MfjE8OaunZEyYYf7TmlI3iEcqXK4A61ChHMQHCMEWTX9HEhwSP4/MSl+mev0 M9cO0xP2892D0fbaRNmVNEpMhp32dcziMrOYWiVBZmDDttt4TJV/KIXtvvE/X9f4ikS6v/cnqp1T bBmSvC6qJad8oDRDO7zv2HwzKB9Jz3yXRnoRrPjCCDcuuTNa7IVsbMNf4F0zLZ5qoQbTN00ljn+y UBPeOMGWHNrDeo2F/515C8kJ0QodJwfrmFGs5lmow6gOzU/CbOds0gEiRQv+PVjjwa+Z5RFCOMQO nNTQB9di96gY0j29orkZgneYl2CbmQ1c/RYOiDQqntAYr6EdEU6c7vBeExW2xziPZnmP/ZqvT8AW hocdnmW4UcgZdOzladg/0vaIE/GiF0bvy7hn2zAqzpfqznRuJlTMTWWk+t+BxdwjRe6Xqbkrbkrr XJdjvnK1Pf/En0f/a8eVoWXqRYabSgG4Bny5zSMsQhyd3uCRcuSY5yi85MYl/hvDZHF5XHlZ9gFX lnSKkqKNrJnamzE3P+qPV+rftQJ+pvlHhV6a5QjptwQBGybQIIqn77BVNAhluSf1tKoo60y5oqSE MZ5ZQYqLIEpgXrLl6H5B8U/ulxTw1Gdtwyp22pb4i4DTkMZeMiNI9JSIyVqEjiLhM0soqLlZEw40 8tzmFJqmfDsYdNRg0kZq4IUB X-Report-Abuse-To: spam@antispammaster.host-h.net List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_028F_01D74B6D.E8D21700 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Good day I'm struggling with a Postgres 13 performance issue and nothing I do seem to help. I have two tables with one having a foreign key to the other. It happens to be for this one client the foreign key is always null, so no violation would be possible. When deleting from the one table the foreign key trigger for constraint takes 20 seconds to run. The tables look as follows table1 id bigint pkey value number table2 (55 mil entries) id bigint pkey table1_id bigint (fkey to table1 id) value number Running delete from table1 where id = 48938 the trigger for constraint runs for 20 seconds Event when doing a simple select from table2 where table1_id = 48938 takes about 8 seconds I've tried the following, but nothing seems to change the outcome: CREATE INDEX table2_idx ON table2(table1_id); CREATE INDEX table2_idx2 ON table2(table1_id) WHERE table1_id IS NOT NULL; CREATE INDEX table2_idx3 ON table2(table1_id) INCLUDE (id) WHERE table1_id IS NOT NULL; alter table table2 alter column table1_id set statistics 10000; None of these steps changes the planner and it would continue to do table scans. Regards Riaan ------=_NextPart_000_028F_01D74B6D.E8D21700 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Good = day

 

I’m struggling with a = Postgres 13 performance issue and nothing I do seem to = help.

 

I have two tables with one = having a foreign key to the other. It happens to be for this one client = the foreign key is always null, so no violation would be possible. When = deleting from the one table the foreign key trigger for constraint takes = 20 seconds to run.

 

The tables look as = follows

 

table1

    = id    bigint pkey

    value number

 

table2 (55 mil = entries)

    = id        bigint = pkey

    table1_id bigint = (fkey to table1 id)

    = value     number

 

Running delete from table1 = where id =3D 48938 the trigger for constraint runs for 20 = seconds

 

Event when doing a simple = select from table2 where table1_id =3D 48938 takes about 8 = seconds

 

I’ve tried the following, = but nothing seems to change the outcome:

CREATE INDEX table2_idx ON = table2(table1_id);

CREATE INDEX = table2_idx2 ON table2(table1_id) WHERE table1_id IS NOT = NULL;

CREATE INDEX table2_idx3 ON = table2(table1_id) INCLUDE (id) WHERE table1_id IS NOT = NULL;

 

alter table table2 alter column = table1_id set statistics 10000;

 

None of these steps changes the = planner and it would continue to do table scans.

 

Regards

Riaan

------=_NextPart_000_028F_01D74B6D.E8D21700--