Re: Very slow Query compared to Oracle / SQL - Server

I've done some testing on different versions of postgres. Unfortunately after the weekend, the problem vanished. The systems are running as usual and the query finishes in 500 MS. It must have been an issue with the VMs or the DISKs. Either way, thank you for your support. Here are btw. some testi

Re: Very slow Query compared to Oracle / SQL - Server

> > Unless I'm overlooking something obvious one result has 500 000 rows the > other 7 000. You are right, it wasn't. I have 2 datasets, one containing 12 mil entries and the other 14 mil entries. I accidentally used the one with 12 mil entries in that table which actually only contains 7000~ entr

Re: Very slow Query compared to Oracle / SQL - Server

Are you sure you're using the same data det ? Unless I'm overlooking something obvious one result has 500 000 rows the other 7 000. >

Re: Very slow Query compared to Oracle / SQL - Server

On Fri, May 7, 2021 at 2:28 PM Peter Geoghegan wrote: > That's a very reasonable interpretation, since the bitmap index scans > themselves just aren't doing that much I/O -- we see that there is > much more I/O for the heap scan, which is likely to be what the > general picture looks like no matte

Re: Very slow Query compared to Oracle / SQL - Server

On Fri, May 7, 2021 at 9:16 AM Justin Pryzby wrote: > In pg13, indexes are de-duplicated by default. > > But I suspect the performance is better because data was reload, and the > smaller indexes are a small, additional benefit. That's a very reasonable interpretation, since the bitmap index scan

Re: Very slow Query compared to Oracle / SQL - Server

Justin Pryzby writes: > On Fri, May 07, 2021 at 05:57:19PM +0200, Semen Yefimenko wrote: >> For testing purposes I set up a separate postgres 13.2 instance on windows. >> To my surprise, it works perfectly fine. Also indexes, have about 1/4 of >> the size they had on 12.6. > In pg13, indexes are

Re: Very slow Query compared to Oracle / SQL - Server

On Fri, May 07, 2021 at 05:57:19PM +0200, Semen Yefimenko wrote: > For testing purposes I set up a separate postgres 13.2 instance on windows. > To my surprise, it works perfectly fine. Also indexes, have about 1/4 of > the size they had on 12.6. In pg13, indexes are de-duplicated by default. But

Re: Very slow Query compared to Oracle / SQL - Server

For testing purposes I set up a separate postgres 13.2 instance on windows. To my surprise, it works perfectly fine. Also indexes, have about 1/4 of the size they had on 12.6. I'll try setting up a new 12.6 instance and see if I can reproduce anything. This explain plan is on a SSD local postgres

Re: Very slow Query compared to Oracle / SQL - Server

On Thu, May 6, 2021 at 10:38 AM Semen Yefimenko wrote: > Hi there, > > I've recently been involved in migrating our old system to SQL Server and > then PostgreSQL. Everything has been working fine so far but now after > executing our tests on Postgres, we saw a very slow running query on a > larg

Re: Very slow Query compared to Oracle / SQL - Server

Is this on windows ? I see a thread that mentions of performance penalty due to parallel worker There is a mailing thread with subject line - Huge performance penalty with parallel queries in Windows x64 v. Linux x64 On Fri, 7 May 2021 at 2:33 PM Semen Yefimenko wrote: > As mentionend in th

Re: Very slow Query compared to Oracle / SQL - Server

As mentionend in the slack comments : SELECT pg_size_pretty(pg_relation_size('logtable')) as table_size, pg_size_pretty(pg_relation_size('idx_entrytype')) as index_size, (pgstattuple('logtable')).dead_tuple_percent; table_size | index_size | dead_tuple_percent ++--

Re: Very slow Query compared to Oracle / SQL - Server

ok one last thing, not to be a PITA, but just in case if this helps. postgres=# SELECT * FROM pg_stat_user_indexes where relname = 'logtable'; postgres=# SELECT * FROM pg_stat_user_tables where relname = 'logtable'; basically, i just to verify if the table is not bloated. looking at *n_live_tup* v

Re: Very slow Query compared to Oracle / SQL - Server

Sorry if I'm cumulatively answering everyone in one E-Mail, I'm not sure how I'm supposed to do it. (single E-Mails vs many) > Can you try tuning by increasing the shared_buffers slowly in steps of > 500MB, and running explain analyze against the query. -- 2500 MB shared buffers - random_page_c

Re: Very slow Query compared to Oracle / SQL - Server

*> Postgres Version : *PostgreSQL 12.2, > ... ON ... USING btree IMHO: The next minor (bugix&security) release is near ( expected ~ May 13th, 2021 ) https://www.postgresql.org/developer/roadmap/ so you can update your PostgreSQL to 12.7 ( + full Reindexing recommended ! ) You can find a lot o

Re: Very slow Query compared to Oracle / SQL - Server

riginal - De: "Semen Yefimenko" <mailto:semen.yefime...@gmail.com> Para: "pgsql-performance" <mailto:pgsql-performance@lists.postgresql.org> Envi

Re: Very slow Query compared to Oracle / SQL - Server

<mailto:luis.robe...@siscobra.com.br> wrote: - Mensagem original - De: "Semen Yefimenko" <mailto:semen.yefime...@gmail.com> Para: "pgsql-performance"

Re: Very slow Query compared to Oracle / SQL - Server

On Thu, May 06, 2021 at 04:38:39PM +0200, Semen Yefimenko wrote: > Hi there, > > I've recently been involved in migrating our old system to SQL Server and > then PostgreSQL. Everything has been working fine so far but now after > executing our tests on Postgres, we saw a very slow running query on

Re: Very slow Query compared to Oracle / SQL - Server

ai 2021 um 20:20 Uhr schrieb Alexey M Boltenkov <padreb...@yandex.ru>: On 05/06/21 21:15, Alexey M Boltenkov wrote: On 05/06/21 19:11, luis.robe...@siscobra.com.br wrote: ----- Mensagem original - De: "

Re: Very slow Query compared to Oracle / SQL - Server

I am not sure, if the goal is just for the specific set of predicates or performance in general. Also from the explain plan, it seems there is still a significant amount of buffers read vs hit. That would constitute i/o and may add to slow result. What is the size of the table and the index ? Is

Re: Very slow Query compared to Oracle / SQL - Server

På torsdag 06. mai 2021 kl. 20:59:34, skrev Semen Yefimenko < semen.yefime...@gmail.com >: Yes, rewriting the query with an IN clause was also my first approach, but I didn't help much. The Query plan did change a little bit but the performance was not impacted

Re: Very slow Query compared to Oracle / SQL - Server

M Boltenkov < padreb...@yandex.ru>: > On 05/06/21 21:15, Alexey M Boltenkov wrote: > > On 05/06/21 19:11, luis.robe...@siscobra.com.br wrote: > > ----- Mensagem original - > > De: "Semen Yefimenko" > Para: "pgsql-performance" > > Enviadas:

Re: Very slow Query compared to Oracle / SQL - Server

On 05/06/21 21:15, Alexey M Boltenkov wrote: On 05/06/21 19:11, luis.robe...@siscobra.com.br wrote: - Mensagem original - De: "Semen Yefimenko" Para: "pgsql-performance" Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39 Assunto: Very slow Query compared to Oracle /

Re: Very slow Query compared to Oracle / SQL - Server

On 05/06/21 19:11, luis.robe...@siscobra.com.br wrote: - Mensagem original - De: "Semen Yefimenko" Para: "pgsql-performance" Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39 Assunto: Very slow Query compared to Oracle / SQL - Server SELECT column1,..., column

Re: Very slow Query compared to Oracle / SQL - Server

- Mensagem original - > De: "Semen Yefimenko" > Para: "pgsql-performance" > Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39 > Assunto: Very slow Query compared to Oracle / SQL - Server > SELECT column1,..., column54 where ((entrytype = 4000

Very slow Query compared to Oracle / SQL - Server

Hi there, I've recently been involved in migrating our old system to SQL Server and then PostgreSQL. Everything has been working fine so far but now after executing our tests on Postgres, we saw a very slow running query on a large table in our database. I have tried asking on other platforms but