Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-08 Thread Frank Millman
On 7 October 2017 at 2:49 PM, David Rowley wrote: > > Yeah, PostgreSQL does not make any effort to convert subqueries in the > target list into joins. SQL server does. [...] > You'll probably find it'll run faster if you convert the subquery in > the target list into a join with a GROUP BY, like

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-07 Thread David Rowley
On 7 October 2017 at 22:34, Frank Millman wrote: > 4. Select from ar_trans_due including join to ar_trans, > plus sub_select from ar_trans_alloc including join to ar_trans > SELECT *, > (SELECT SUM(c.alloc_cust) > FROM ar_trans_alloc c > LEFT JOIN ar_tra

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-07 Thread Frank Millman
On 5 Oct 2017, at 9:51 AM, Frank Millman wrote: > > I should have re-stated the reason for my original post. > > Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql > Server, 1.0 seconds on SQLite3, and 1607 seconds, or 26 minutes, on > PostgreSQL 9.4.4. > I will give

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-05 Thread Frank Millman
On 5 Oct 2017, at 8:20 AM, Frank Millman wrote: > If anyone wants to take this further, maybe this is a good place to start. I should have re-stated the reason for my original post. Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql Server, 1.0 seconds on SQLite3, and 1

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-04 Thread Frank Millman
On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote: > On 2 Oct 2017, at 8:32, Frank Millman wrote: > > > On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > > > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > > Something is not adding up here. Can you EXPLAIN ANALYZE the

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-04 Thread Frank Millman
On Wednesday, October 4, 2017 06:07 PM Jan de Visser wrote: > On Monday, October 2, 2017 2:32:34 AM EDT Frank Millman wrote: > > > > Just checking – is this under investigation, or is this thread considered > > closed? > > That's not how it works. This is a community list; if somebody finds yo

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-04 Thread Alban Hertroys
> On 2 Oct 2017, at 8:32, Frank Millman wrote: > > > From: Frank Millman > Sent: Friday, September 22, 2017 7:34 AM > To: pgsql-general@postgresql.org > Subject: Re: a JOIN to a VIEW seems slow > > > On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > > > On Thu, Sep 21, 2017 at

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-04 Thread Jan de Visser
On Monday, October 2, 2017 2:32:34 AM EDT Frank Millman wrote: > From: Frank Millman > Sent: Friday, September 22, 2017 7:34 AM > To: pgsql-general@postgresql.org > Subject: Re: a JOIN to a VIEW seems slow > > On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > On Thu, Sep 21, 2017 at 3:54

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-01 Thread Frank Millman
From: Frank Millman Sent: Friday, September 22, 2017 7:34 AM To: pgsql-general@postgresql.org Subject: Re: a JOIN to a VIEW seems slow On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > Something is not adding up here. Ca

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-23 Thread Frank Millman
Frank Millman wrote: > > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > Something is not adding up here. Can you EXPLAIN ANALYZE the 26 minute > > query? > Here it is - > > https://explain.depesz.com/s/cwm > There is one thing I have not mentioned. I am pretty sure it has

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-22 Thread Frank Millman
Merlin Moncure wrote: On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman wrote: > > SELECT q.cust_row_id, > SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END > ) AS "balance_curr AS [DECTEXT]", > SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31' > THEN q.b

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-22 Thread Merlin Moncure
On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman wrote: > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: >> >> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman >> > wrote: >> > >> >> I did not get any response to this, but I am still persevering, and feel >> >> that I am getting closer. In

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-21 Thread Frank Millman
On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > Something is not adding up here. Can you EXPLAIN ANALYZE the 26 minute > query? Here it is - https://explain.depesz.com/s/cwm Frank

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-21 Thread Merlin Moncure
On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman wrote: > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: >> >> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman >> > wrote: >> > >> >> I did not get any response to this, but I am still persevering, and feel >> >> that I am getting closer. In

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-21 Thread Frank Millman
On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman wrote: > > > > I did not get any response to this, but I am still persevering, and feel > > that I am getting closer. Instead of waiting 26 minutes for a result, I > > realise that I can le

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-21 Thread Merlin Moncure
On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman wrote: > On 2017-09-18 Frank Millman wrote: >> >> Here are the timings for running the query on identical data sets using >> Postgresql, Sql Server, and Sqlite3 - >> >> PostgreSQL - >> Method 1 - 0.28 sec >> Method 2 – 1607 sec, or 26 minutes

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-20 Thread Frank Millman
On 2017-09-18 Frank Millman wrote: > > Here are the timings for running the query on identical data sets using > Postgresql, Sql Server, and Sqlite3 - > > PostgreSQL - > Method 1 - 0.28 sec > Method 2 – 1607 sec, or 26 minutes > > Sql Server - > Method 1 – 0.33 sec > Method 2 –

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-18 Thread Frank Millman
Merlin Moncure wrote: On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule wrote: > > > > > The PostgreSQL cannot to push join - in slow case, the UNIONS should be done > > first - and it requires full scan ar_tran_inv - used filter (posted AND > > (deleted_id = 0) is not too effective - maybe some comp

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-18 Thread Merlin Moncure
On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule wrote: > 2017-09-14 15:09 GMT+02:00 Pavel Stehule : >> >> >> >> 2017-09-14 14:59 GMT+02:00 Frank Millman : >>> >>> Pavel Stehule wrote: >>> >>> 2017-09-14 10:14 GMT+02:00 Frank Millman : Hi all This is a follow-up to a recent quest

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-17 Thread Frank Millman
2017-09-14 15:09 GMT+02:00 Pavel Stehule : 2017-09-14 14:59 GMT+02:00 Frank Millman : Pavel Stehule wrote: 2017-09-14 10:14 GMT+02:00 Frank Millman : Hi all This is a follow-up to a recent question I posted regarding a slow query. I thought that the slowness was cause

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 Thread Pavel Stehule
2017-09-14 15:09 GMT+02:00 Pavel Stehule : > > > 2017-09-14 14:59 GMT+02:00 Frank Millman : > >> Pavel Stehule wrote: >> >> 2017-09-14 10:14 GMT+02:00 Frank Millman : >> >>> Hi all >>> >>> This is a follow-up to a recent question I posted regarding a slow >>> query. I thought that the slowness was

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 Thread Pavel Stehule
2017-09-14 14:59 GMT+02:00 Frank Millman : > Pavel Stehule wrote: > > 2017-09-14 10:14 GMT+02:00 Frank Millman : > >> Hi all >> >> This is a follow-up to a recent question I posted regarding a slow query. >> I thought that the slowness was caused by the number of JOINs in the query, >> but with yo

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 Thread Frank Millman
Pavel Stehule wrote: 2017-09-14 10:14 GMT+02:00 Frank Millman : Hi all This is a follow-up to a recent question I posted regarding a slow query. I thought that the slowness was caused by the number of JOINs in the query, but with your assistance I have found the true reason. I said in the

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-09-14 Thread Pavel Stehule
2017-09-14 10:14 GMT+02:00 Frank Millman : > Hi all > > This is a follow-up to a recent question I posted regarding a slow query. > I thought that the slowness was caused by the number of JOINs in the query, > but with your assistance I have found the true reason. I said in the > previous thread t