Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do? - Mailing list pgsql-performance

From Albe Laurenz
Subject Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do?
Date
Msg-id A737B7A37273E048B164557ADEF4A58B53A069B0@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to [PERFORM] Please help with a slow query: there are millions of records, whatcan we do?  (Pat Maddox <pat@adorable.io>)
List pgsql-performance
Pat Maddox wrote:
> I’ve been asked to help with a project dealing with slow queries. I’m brand new to the project, so I
> have very little context. I’ve gathered as much information as I can.
> 
> I’ve put the schema, query, and explain info in gists to maintain their formatting.
> 
> We are stumped with this slow query right now. I could really use some help looking for ways to speed
> it up.

I don't know if the plan can be improved; it has to retrieve and sort 347014 rows,
most of which are read from diak, so it will take some time.

One thing I notice is that some statistics seem to be bad (the estimate for
the index scan on "permissions" is off the mark), so maybe you can ANALYZE
both tables (perhaps with higher "default_statistics_target") and see if that
changes anything.

Is there any chance you could give the machine lots of RAM?
That would speed up the bitmap heap scan (but not the sort).

Yours,
Laurenz Albe

pgsql-performance by date:

Previous
From: Pat Maddox
Date:
Subject: [PERFORM] Please help with a slow query: there are millions of records, whatcan we do?
Next
From: Jeff Janes
Date:
Subject: Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do?