MIME-Version: 1.0
References: <431223779.107307.1721971823963.ref@mail.yahoo.com>
 <431223779.107307.1721971823963@mail.yahoo.com> <CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com>
In-Reply-To: <CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Sat, 27 Jul 2024 15:27:08 +1200
Message-ID: <CAApHDvpX3aw3o_+09-mJRGY59UH8DKMBB6Y6wVq8bVp+k7QogA@mail.gmail.com>
Subject: Re: Slow performance
To: Francisco Olarte <folarte@peoplecall.com>
Cc: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>, 
	Postgresql General Group <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpX3aw3o_%2B09-mJRGY59UH8DKMBB6Y6wVq8bVp%2Bk7QogA%40mail.gmail.com>
Precedence: bulk

On Fri, 26 Jul 2024 at 19:55, Francisco Olarte <folarte@peoplecall.com> 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