Re: Slow query and wrong row estimates for CTE

2021-02-17 Thread Yoan SULTAN
*You are totally right, the max(score_value) FILTER (WHERE score_name = 'student_performance_index') in the SELECT clause is redundant.* Le mer. 17 févr. 2021 à 21:33, Dane Foster a écrit : > On Wed, Feb 17, 2021 at 1:37 PM Yoan SULTAN wrote: > >> *Hi all, * >> >> *This is my first post on th

Re: Slow query and wrong row estimates for CTE

2021-02-17 Thread Dane Foster
On Wed, Feb 17, 2021 at 1:37 PM Yoan SULTAN wrote: > *Hi all, * > > *This is my first post on this mailing list, I really enjoy it.* > *I wanted to add some details and answers to this disccusion.* > I'm happy you've decided to join the conversation and about the fact that you've opened up an ent

Re: Slow query and wrong row estimates for CTE

2021-02-17 Thread Yoan SULTAN
*Hi all, * *This is my first post on this mailing list, I really enjoy it.* *I wanted to add some details and answers to this disccusion.* 17 févr. 2021 à 17:52, Dane Foster a écrit : > > A small update (see below/inline). > > > On Tue, Feb 16, 2021 at 2:11 PM Dane Foster wrote: > >> Short co

Re: Slow query and wrong row estimates for CTE

2021-02-17 Thread Dane Foster
A small update (see below/inline). On Tue, Feb 16, 2021 at 2:11 PM Dane Foster wrote: > Short conclusion: > Switching from CTEs to temporary tables and analyzing reduced the runtime > from 15 minutes to about 1.5 minutes. > > > Longer conclusion: > > @Justin Pryzby > >- I experimented w/ m

Re: Slow query and wrong row estimates for CTE

2021-02-16 Thread Dane Foster
Short conclusion: Switching from CTEs to temporary tables and analyzing reduced the runtime from 15 minutes to about 1.5 minutes. Longer conclusion: @Justin Pryzby - I experimented w/ materializing the CTEs and it helped at the margins but did not significantly contribute to a reduction

Re: Slow query and wrong row estimates for CTE

2021-02-16 Thread Dane Foster
On Tue, Feb 16, 2021 at 10:13 AM Michael Lewis wrote: >Sort Method: external >> merge Disk: 30760kB >>Worker 0: Sort >> Method: external merge Disk: 30760kB >>

Re: Slow query and wrong row estimates for CTE

2021-02-16 Thread Dane Foster
On Mon, Feb 15, 2021 at 5:32 PM Justin Pryzby wrote: > ... > > Without looking closely, an index might help: student_id,assignment_id > That'd avoid the sort, and maybe change the shape of the whole plan. > I tried that prior to posting on the forum and it didn't make a difference. 🙁 I'll try yo

Re: Slow query and wrong row estimates for CTE

2021-02-16 Thread Michael Lewis
> >Sort Method: external > merge Disk: 30760kB >Worker 0: Sort Method: > external merge Disk: 30760kB >Worker 1: Sort Method: > external me

Re: Slow query and wrong row estimates for CTE

2021-02-15 Thread Justin Pryzby
On Mon, Feb 15, 2021 at 12:49:29PM -0500, Dane Foster wrote: > PostgreSQL version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit > EXPLAIN (ANALYZE, BUFFERS) > WITH max_spi AS ( Since v12, CTEs are usually inlined by default. I suspect i

Slow query and wrong row estimates for CTE

2021-02-15 Thread Dane Foster
Hello, I'm seeking guidance in how to improve the performance of a slow query and to have some other sets of eyes confirm that what I wrote does what I intend. According to the PostgreSQL wiki there is a set of metadata that I should provide to help you help me. So let's begin there. PostgreSQL