public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Francisco Olarte <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Postgresql General Group <[email protected]>
Subject: Re: Slow performance
Date: Sat, 27 Jul 2024 15:27:08 +1200
Message-ID: <CAApHDvpX3aw3o_+09-mJRGY59UH8DKMBB6Y6wVq8bVp+k7QogA@mail.gmail.com> (raw)
In-Reply-To: <CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com>

On Fri, 26 Jul 2024 at 19:55, Francisco Olarte <[email protected]> wrote:
> "              ->  Index Scan using
> ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p
> (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702
> rows=41764 loops=1)"
> "                    Index Cond: (((companycode)::text = '100'::text)
> AND ((examheaderfk)::text =
> 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))"
>
> Not an explain expert, but if i read correctly an index scan expecting
> 1 row recovers 41674, which hints at bad statistics ( or skewed data
> distribution and bad luck )

You have correctly identified the reason the poor plan was chosen. If
that row estimate was anything higher than 1, that plan wouldn't be
picked.

If ANALYZE cl_student_semester_subject; does not fix the issue, then
increasing the statistics targets with something like:

alter table cl_student_semester_subject alter column companycode set
(default_statistics_target = 1000);
alter table cl_student_semester_subject alter column examheaderfk set
(default_statistics_target = 1000);
analyze cl_student_semester_subject;

(Warning, additional statistics targets can slow down planning a little)

or if that does not help and there's some correlation between those
columns and/or the values in question, then maybe the following might
help get a more accurate estimate:

create statistics on companycode, examheaderfk from cl_student_semester_subject;
analyze cl_student_semester_subject;

David

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Slow performance
  In-Reply-To: <CAApHDvpX3aw3o_+09-mJRGY59UH8DKMBB6Y6wVq8bVp+k7QogA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox