Re: Slow performance

On Fri, 26 Jul 2024 at 19:55, Francisco Olarte <folarte(at)peoplecall(dot)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