Обсуждение: Re: Slow while inserting and retrieval (compared to SQL Server)

----- Mensagem original -----
> De: sivapostgres@yahoo.com
> Para: "pgsql-general" <pgsql-general@lists.postgresql.org>
> Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:01:15
> Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)
> 
> Hello, 
> 
> Using Postgres 11 in Windows Server 2012 & Powerbuilder 
> Working from the same machine where Postgres 11 is installed. So no chance for any network issue, I feel. 
> No setup/config change done. Just working with all the default settings. 
> With no data in any of the 179 tables in that database. 
> 
> To populate some basic data we try to insert few records (max 4 records) in few tables (around 6 tables) from one
window.We feel that the insert time taken is longer than the time taken while using Sql Server. We tested almost a
similarwindow that updated the similar table(s) in SQL server, which was > > faster. With Postgres database, we need to
waitfor a couple of seconds before the insert/update is over, which we didn't feel in Sql Server. 
 
> 
> I feel that some changes in settings might improve this speed, but with not much knowledge in Postgres I struggle to
findout those setup values. 
 
> 
> Any help in improving the speed is really appreciated. 
> 
> Happiness Always 
> BKR Sivaprakash 

Can you run EXPLAIN (ANALYZE,BUFFERS) on the INSERT command and post the results here? Usually inserting this many
recordsshould be instantaneous.
 

----- Mensagem original -----
> De: "sivapostgres" <sivapostgres@yahoo.com>
> Para: "luis.roberto" <luis.roberto@siscobra.com.br>
> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>
> Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:54:18
> Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

> Hello,
> Yes, that's what I feel. With no records in any tables, working from the same
> machine where PG has been installed, with only one user working, inserting few
> records (10 records in total, in all 6 tables) should not take this much time.

> I'll be inserting records from PowerBuilder applications, and how to catch the
> result of Explain. OR should I run all insert commands in PG Admin or so and
> catch those results?

> As I'm new to PG, any documentation/help in this direction will be useful.

> Happiness Always
> BKR Sivaprakash

> On Wednesday, 17 February, 2021, 05:35:43 pm IST, <luis.roberto@siscobra.com.br>
> wrote:


You can run it wherever you prefer. I'm not familiar with PowerBuilder, so I'd say PGAdmin.
BTW, if you run the INSERTs on PGAdmin, do you still seeing slow timings?

sivapostgres@yahoo.com schrieb am 17.02.2021 um 13:01:
> To populate some basic data we try to insert few records (max 4
> records) in few tables (around 6 tables) from one window.  We feel
> that the insert time taken is longer than the time taken while using
> Sql Server.   We tested almost a similar window that updated the
> similar table(s) in SQL server, which was faster.  With Postgres
> database, we need to wait for a couple of seconds before the
> insert/update is over, which we didn't feel in Sql Server.


Are you doing single-row inserts like:

    insert into ... values (..);
    insert into ... values (..);
    insert into ... values (..);
    insert into ... values (..);

or are you doing multi-row inserts like this:

    insert into ... values (..), (..), (..), (..);

Typically the latter will perform much better (especially if autocommit is enabled)

sivapostgres@yahoo.com schrieb am 17.02.2021 um 14:27:
> We use datawindows.  Datawindows will send the required DML
> statements to the database.
> And it sent in format 1 <single row update>.
>
> IN start of the application, Autocommit set to True.
> Before update of any table(s)
> Autocommit is set to False
> Insert/Update/Delete records
> If success commit else rollback
> Autocommit is set to True
>
> This has been followed for decades and it's working fine with Sql
> server.
>
> Here we are trying to insert just 10 records spread across 6 tables,
> which is taking more time.. that's what we feel.   The similar work
> in SQL Server takes much less time < as if no wait is there >.
>

The rollback can't work if autocommit is set to true.
Did you mean "IN start of the application, Autocommit set to FALSE"?

However these simple statements shouldn't take substantially longer
on Postgres than on SQL Server.

If you are DELETEing from tables with foreign keys: are all FK columns indexed?
That can make a huge difference if you delete from the parent table,
but the FK columns on the children aren't indexed.

It would be really interesting to see the execution plans generated
"explain (analyze)" for the DELETE and UPDATE statements.

ATTENTION: explain (analyze) actually runs that statement so it will do the
deletes or updates. Make sure you run that in a transaction so you can rollback.

Thomas

On 2021-02-17 12:54:18 +0000, sivapostgres@yahoo.com wrote:
> Yes, that's what I feel.  With no records in any tables, working from the same
> machine where PG has been installed, with only one user working, inserting few
> records (10 records in total, in all 6 tables)  should not take this much
> time.

How much time is "this much time"? Are we talking a few milliseconds
here? Less? More? Much more?

It's hard to give advice if you don't tell us more than "slower than SQL
server". Please be specific. Use actual numbers.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

> De: "sivapostgres" <sivapostgres@yahoo.com>
> Para: "Benedict Holland" <benedict.m.holland@gmail.com>
> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>, "Thomas Kellerer" <shammat@gmx.net>
> Enviadas: Quarta-feira, 17 de fevereiro de 2021 11:09:38
> Assunto: Re: Slow while inserting and retrieval (compared to SQL Server)

> So far no performance tuning done for sql server.  It works fine for the load.
> Even the express edition which is free, works better.  I don't think postgres
> will be so slow to insert such a low number of records in an empty database.
> I'll be preparing the required sql statements to insert those records in pgadmin
> and see the timings, tomorrow.
>
>    On Wednesday, 17 February, 2021, 07:29:29 pm IST, Benedict Holland
>    <benedict.m.holland@gmail.com> wrote:
>
> Sql server is a 10k dollar to 1 million dollar application (or more) that is
> specifically optimized for windows and had limited to no support anywhere else.
> Postgres is free and from my experience, comes within 5% of any other dbms.
> Inserting 1 row at a time with auto commit on will be a bit slow but it
> shouldn't be noticeable. What times are you seeing if you do this with pgadmin4
> compared to sql server? Also, have you done any performance tuning for postgres
> server? There are many documents detailing performance tuning your servers,
> like you probably did, at some point, with your sql server.
> Thanks,Ben
> On Wed, Feb 17, 2021, 8:28 AM sivapostgres@yahoo.com <sivapostgres@yahoo.com>
> wrote:
>
> We use datawindows.  Datawindows will send the required DML statements to the
> database.  And it sent in format 1 <single row update>.
> IN start of the application, Autocommit set to True.Before update of any
> table(s)Autocommit is set to FalseInsert/Update/Delete recordsIf success commit
> else rollbackAutocommit is set to True
> This has been followed for decades and it's working fine with Sql server.
> Here we are trying to insert just 10 records spread across 6 tables, which is
> taking more time.. that's what we feel.   The similar work in SQL Server takes
> much less time < as if no wait is there >.
>    On Wednesday, 17 February, 2021, 06:48:35 pm IST, Thomas Kellerer
>    <shammat@gmx.net> wrote:
>
> sivapostgres@yahoo.com schrieb am 17.02.2021 um 13:01:
>> To populate some basic data we try to insert few records (max 4
>> records) in few tables (around 6 tables) from one window.  We feel
>> that the insert time taken is longer than the time taken while using
>> Sql Server.  We tested almost a similar window that updated the
>> similar table(s) in SQL server, which was faster.  With Postgres
>> database, we need to wait for a couple of seconds before the
>> insert/update is over, which we didn't feel in Sql Server.
>
>
> Are you doing single-row inserts like:
>
>    insert into ... values (..);
>    insert into ... values (..);
>    insert into ... values (..);
>    insert into ... values (..);
>
> or are you doing multi-row inserts like this:
>
>    insert into ... values (..), (..), (..), (..);
>
> Typically the latter will perform much better (especially if autocommit is
> enabled)

Please provide the EXPLAIN ANALYZE plans so we can take a look at what is causing these 'slow' inserts.