Re: Query Performance / Planner estimate off

2020-10-27 Thread Mats Olsen
On 10/21/20 5:35 PM, Sebastian Dressler wrote: Hi Mats, Happy to help. On 21. Oct 2020, at 16:42, Mats Olsen > wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote:

Re: Query Performance / Planner estimate off

2020-10-23 Thread Mats Olsen
Thanks for your response Justin. On 10/22/20 3:48 PM, Justin Pryzby wrote: On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote: On 10/22/20 8:37 AM, Justin Pryzby wrote: These look redundant (which doesn't matter for this the query): Partition key: RANGE (block_number) Indexes: "

Re: Query Performance / Planner estimate off

2020-10-22 Thread Justin Pryzby
On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote: > On 10/22/20 8:37 AM, Justin Pryzby wrote: > > These look redundant (which doesn't matter for this the query): > > > > Partition key: RANGE (block_number) > > Indexes: > > "transactions_block_number_btree" btree (block_number DESC)

Re: Query Performance / Planner estimate off

2020-10-22 Thread Mats Olsen
On 10/22/20 8:37 AM, Justin Pryzby wrote: On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: On 20. Oct 2020, at 11:37, Mats Julian Olsen https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min):

Re: Query Performance / Planner estimate off

2020-10-21 Thread Justin Pryzby
On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote: > On 10/21/20 2:38 PM, Sebastian Dressler wrote: > > > On 20. Oct 2020, at 11:37, Mats Julian Olsen > > > > > [...] > > > > > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > > > > > >

Re: Query Performance / Planner estimate off

2020-10-21 Thread Mats Olsen
On 10/21/20 5:29 PM, Michael Lewis wrote: On Wed, Oct 21, 2020, 8:42 AM Mats Olsen > wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...

Re: Query Performance / Planner estimate off

2020-10-21 Thread Sebastian Dressler
Hi Mats, Happy to help. On 21. Oct 2020, at 16:42, Mats Olsen mailto:m...@duneanalytics.com>> wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...] 1) Vanilla plan (16 min) : https://explain.de

Re: Query Performance / Planner estimate off

2020-10-21 Thread Michael Lewis
On Wed, Oct 21, 2020, 8:42 AM Mats Olsen wrote: > > On 10/21/20 2:38 PM, Sebastian Dressler wrote: > > Hi Mats, > > On 20. Oct 2020, at 11:37, Mats Julian Olsen > wrote: > > [...] > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > 2) enable_nestloop=off (4 min): https://explain.d

Re: Query Performance / Planner estimate off

2020-10-21 Thread Mats Olsen
On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen > wrote: [...] 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min): https://explain

Re: Query Performance / Planner estimate off

2020-10-21 Thread Sebastian Dressler
Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...] 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK 3) enable_nestloop=off; enable_seqscan=off (2 min): https://explain.

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Olsen
On 10/20/20 6:51 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 16:50, Mats Olsen >: On 10/20/20 3:04 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen mailto:m...@duneanalytics.com>>: I'm looking for some help to man

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Olsen
On 10/20/20 3:22 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >: I'm looking for some help to manage queries against two large tables. Also, can you enable `track_io_timing` (no restart required) and provide output of `EXPLAIN (a

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Olsen
On 10/20/20 3:40 PM, Sushant Pawar wrote: Looping in the main group ID. Regards Sushant On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar > wrote: On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: Dear Postgres

Re: Query Performance / Planner estimate off

2020-10-20 Thread Victor Yegorov
вт, 20 окт. 2020 г. в 16:50, Mats Olsen : > On 10/20/20 3:04 PM, Victor Yegorov wrote: > > вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > >> I'm looking for some help to manage queries against two large tables. >> > > Can you tell the version you're running currently and the output of this > q

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Olsen
On 10/20/20 11:37 AM, Mats Julian Olsen wrote: Dear Postgres community, I'm looking for some help to manage queries against two large tables. Context: We run a relatively large postgresql instance (5TB, 32 vCPU, 120GB RAM) with a hybrid transactional/analytical workload. Data is written in b

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Olsen
On 10/20/20 3:04 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >: I'm looking for some help to manage queries against two large tables. Can you tell the version you're running currently and the output of this query, please?    

Re: Query Performance / Planner estimate off

2020-10-20 Thread Sushant Pawar
Looping in the main group ID. Regards Sushant On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar wrote: > > On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen > wrote: > >> Dear Postgres community, >> >> I'm looking for some help to manage queries against two large tables. >> >> Context: >> We run a

Re: Query Performance / Planner estimate off

2020-10-20 Thread Victor Yegorov
вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > I'm looking for some help to manage queries against two large tables. > Also, can you enable `track_io_timing` (no restart required) and provide output of `EXPLAIN (analyze, buffers, settings)` for all 3 variants, please? (I assume you're on 12+.

Re: Query Performance / Planner estimate off

2020-10-20 Thread Victor Yegorov
вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > I'm looking for some help to manage queries against two large tables. > Can you tell the version you're running currently and the output of this query, please? select name,setting,source from pg_settings where source not in ('default','overr

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Julian Olsen
On Tue, Oct 20, 2020 at 11:16 AM Pavel Stehule wrote: > > > út 20. 10. 2020 v 13:09 odesílatel Mats Julian Olsen < > m...@duneanalytics.com> napsal: > >> >> >> On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule >> wrote: >> >>> >>> >>> út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < >>> m...@

Re: Query Performance / Planner estimate off

2020-10-20 Thread Pavel Stehule
út 20. 10. 2020 v 13:09 odesílatel Mats Julian Olsen napsal: > > > On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule > wrote: > >> >> >> út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < >> m...@duneanalytics.com> napsal: >> >>> On Tue, Oct 20, 2020 at 9:50 AM David Rowley >>> wrote: >>>

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Julian Olsen
On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule wrote: > > > út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < > m...@duneanalytics.com> napsal: > >> On Tue, Oct 20, 2020 at 9:50 AM David Rowley >> wrote: >> >>> On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen >>> wrote: >>> > >>> > The crux

Re: Query Performance / Planner estimate off

2020-10-20 Thread Pavel Stehule
út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen napsal: > On Tue, Oct 20, 2020 at 9:50 AM David Rowley wrote: > >> On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen >> wrote: >> > >> > The crux of our issue is that the query planner chooses a nested loop >> join for this query. Essentially m

Re: Query Performance / Planner estimate off

2020-10-20 Thread Mats Julian Olsen
On Tue, Oct 20, 2020 at 9:50 AM David Rowley wrote: > On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen > wrote: > > > > The crux of our issue is that the query planner chooses a nested loop > join for this query. Essentially making this query (and other queries) take > a very long time to complet

Re: Query Performance / Planner estimate off

2020-10-20 Thread David Rowley
On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen wrote: > > The crux of our issue is that the query planner chooses a nested loop join > for this query. Essentially making this query (and other queries) take a very > long time to complete. In contrast, by toggling `enable_nestloop` and > `enable

Query Performance / Planner estimate off

2020-10-20 Thread Mats Julian Olsen
Dear Postgres community, I'm looking for some help to manage queries against two large tables. Context: We run a relatively large postgresql instance (5TB, 32 vCPU, 120GB RAM) with a hybrid transactional/analytical workload. Data is written in batches every 15 seconds or so, and the all queryable