Re: SELECT is faster on SQL Server

On 2021-03-19 7:11 PM, Thomas Kellerer wrote: Frank Millman schrieb am 19.03.2021 um 10:16: cl_bal selects WHERE tran_date <= '2018-03-31'. op_bal selects WHERE tran_date < '2018-03-01'. The second one could be written as WHERE tran_date <= '2018-02-28', but I don't think that would make a

Re: SELECT is faster on SQL Server

On 2021-03-19 5:32 PM, Jehan-Guillaume de Rorthais wrote: On Fri, 19 Mar 2021 14:28:27 +0200 Frank Millman wrote: [...] Execution of my main query has improved from 50ms to 33ms. Sql Server takes 25ms, but this is much better than it was. [...] Here is the new EXPLAIN ANALYSE -

Re: SELECT is faster on SQL Server

On 2021-03-19 4:38 PM, Tom Lane wrote: Frank Millman writes: However, the bizarre thing is that I have simply restored the index to what it was in the first place. If you look at the table definition in my original message you can see that all the columns were included in the index. But the q

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 10:16: Very often "distinct on ()" is faster in Postgres compared to the equivalent solution using window functions The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing - at least I can't spot a difference. If that is correct,

Re: SELECT is faster on SQL Server

On Fri, 19 Mar 2021 14:28:27 +0200 Frank Millman wrote: > [...] > Execution of my main query has improved from 50ms to 33ms. Sql Server > takes 25ms, but this is much better than it was. > > [...] > > Here is the new EXPLAIN ANALYSE - > >QUERY PLAN > --

Re: SELECT is faster on SQL Server

Frank Millman writes: > However, the bizarre thing is that I have simply restored the index to > what it was in the first place. If you look at the table definition in > my original message you can see that all the columns were included in > the index. But the query did not use it as a covering

Re: SELECT is faster on SQL Server

On 2021-03-19 12:58 PM, Frank Millman wrote: QUERY PLAN  Merge Left Join  (cost=1401.00..1401.12 rows=1 width=132) (actual time=3.595..3

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 11:58 odesílatel Frank Millman napsal: > > On 2021-03-19 12:00 PM, Pavel Stehule wrote: > > > In this query the most slow operation is query planning. You try to do > tests on almost empty tables. This has no practical sense. You should test > queries on tables with size similar

Re: SELECT is faster on SQL Server

On Fri, Mar 19, 2021 at 12:58:10PM +0200, Frank Millman wrote: > On 2021-03-19 12:00 PM, Pavel Stehule wrote: > > In this query the most slow operation is query planning. You try to do > tests on almost empty tables. This has no practical sense. > You should test queries on tables with size s

Re: SELECT is faster on SQL Server

On 2021-03-19 12:00 PM, Pavel Stehule wrote: In this query the most slow operation is query planning. You try to do tests on almost empty tables. This has no practical sense. You should test queries on tables with size similar to production size. Sorry about that. I hope this one is better

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 10:22 odesílatel Frank Millman napsal: > > On 2021-03-19 10:56 AM, Pavel Stehule wrote: > > > > pá 19. 3. 2021 v 9:53 odesílatel Frank Millman > napsal: > >> >> On 2021-03-19 10:29 AM, Thomas Kellerer wrote: >> > Frank Millman schrieb am 19.03.2021 um 09:19: >> >> This may be a

Re: SELECT is faster on SQL Server

On 2021-03-19 10:56 AM, Pavel Stehule wrote: pá 19. 3. 2021 v 9:53 odesílatel Frank Millman > napsal: On 2021-03-19 10:29 AM, Thomas Kellerer wrote: > Frank Millman schrieb am 19.03.2021 um 09:19: >> This may be a non-issue, and I don't want to waste y

Re: SELECT is faster on SQL Server

On 2021-03-19 11:04 AM, Thomas Kellerer wrote: Frank Millman schrieb am 19.03.2021 um 09:52: I am writing a cross-platform accounting app, and I test using Sql Server on Windows 10 and PostgreSql on Fedora 31. Performance is usually very similar, with a slight edge to PostgreSql. Now I have a

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 09:52: >>> I am writing a cross-platform accounting app, and I test using Sql >>> Server on Windows 10 and PostgreSql on Fedora 31. Performance is >>> usually very similar, with a slight edge to PostgreSql. Now I have a >>> SELECT which runs over twice as fast

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 9:53 odesílatel Frank Millman napsal: > > On 2021-03-19 10:29 AM, Thomas Kellerer wrote: > > Frank Millman schrieb am 19.03.2021 um 09:19: > >> This may be a non-issue, and I don't want to waste your time. But > perhaps someone can have a look to see if there is anything obvious

Re: SELECT is faster on SQL Server

On 2021-03-19 10:29 AM, Thomas Kellerer wrote: Frank Millman schrieb am 19.03.2021 um 09:19: This may be a non-issue, and I don't want to waste your time. But perhaps someone can have a look to see if there is anything obvious I have missed. I am writing a cross-platform accounting app, and

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 09:19: > This may be a non-issue, and I don't want to waste your time. But perhaps > someone can have a look to see if there is anything obvious I have missed. > > I am writing a cross-platform accounting app, and I test using Sql > Server on Windows 10 and P

SELECT is faster on SQL Server

Hi all This may be a non-issue, and I don't want to waste your time. But perhaps someone can have a look to see if there is anything obvious I have missed. I am writing a cross-platform accounting app, and I test using Sql Server on Windows 10 and PostgreSql on Fedora 31. Performance is usua