SELECT * from simple table with 50k records seems to take 40 seconds

Question

Here's my table:

CREATE TABLE public.logs (
  logid bigint NOT NULL DEFAULT nextval('applog_logid_seq'::regclass),
  applicationname character varying(50),
  loglevel character varying(10),
  logmessage character varying(500),
  stacktrace character varying(4096),
  occurredon timestamp without time zone,
  loggedon timestamp without time zone,
  username character varying(50),
  groupname character varying(50),
  useragent character varying(512),
  CONSTRAINT applog_pkey PRIMARY KEY (logid)
);

When I run SELECT *... on it, it takes 40 seconds to return 50000 rows on my local machine. I have the same table on a local install of SQL Server, and that takes less than a second to return the same amount of data.

I'm in the middle of an evaluation of PostgreSQL for our new stack and this is very concerning to me. Why am I doing wrong/why is PostgreSQL so slow?

Edit:

Here's what I get from EXPLAIN (BUFFERS, ANALYZE, TIMING OFF) SELECT * FROM public.logs:

So it looks like the server's going to execute this in about 6 ms. I guess that means all the overhead is in pgAdmin III, but how is SSMS able to do this so much faster?

Answer 1

Thanks so much for everyone's help in talking me down from the cliff:)

I composed a node console app app that puts my concerns to bed. In fact, my Postres instance beats SQL Server by about 50% (as @Guillaume F. pointed out). In the same client here are the results:

Postres (RDS) query duration: 7062ms

Postres (RDS) rows returned: 50000

Postgres (Local) query duration: 1919ms

Postgres (Local) rows returned: 46154

MSSQL (local) query duration: 4681ms

MSSQL (local) rows returned: 50000

Here's the sample app if anyone's interested in duplicating my results in their own environment:

'use strict';

let pgp = require('pg-promise')();
let db = pgp("postgres://username:password@server:5432/db");
let localdb = pgp("postgres://username:password@server:5432/db");
var mssql = require('mssql');

let start = new Date();
db.query('select * from logs').then((result) => {
    console.log("Postres (RDS) query duration: " + (new Date() - start) + "ms");
    console.log("Postres (RDS) rows returned: " + result.length);
    console.log("");

    let localstart = new Date();
    localdb.query('select * from logs').then((localresult) => {
        console.log("Postgres (Local) query duration: " + (new Date() - localstart) + "ms");
        console.log("Postgres (Local) rows returned: " + localresult.length);
        console.log("");

        var config = {
            user: 'username',
            password: 'password',
            server: 'server', // You can use 'localhost\\instance' to connect to named instance 
            database: 'db'
        };

        mssql.connect(config).then(function () {

            // Query 
            let localMSSqlStart = new Date();
            new mssql.Request().query('select TOP 50000 * from dbo.AppLog ORDER BY 1 DESC').then(function (recordset) {
                console.log("MSSQL (local) query duration: " + (new Date() - localMSSqlStart) + "ms");
                console.log("MSSQL (local) rows returned: " + result.length);
                console.log("");
            }).catch(function (err) {

                // ... query error checks
                console.log("Problem querying MSSQL: " + err);
            });
        }).catch(function (err) {

            // ... connect error checks 
            console.log("Problem connecting to MSSQL: " + err);
        });
    });
});

EDIT: (by pg-promise author)

On a side note, this is just to show how to benchmark PostgreSQL in a more civilized way:

let pgp = require('pg-promise')();
let db = pgp("postgres://username:password@server:5432/db");
let localdb = pgp("postgres://username:password@server:5432/db");

db.result('select * from logs')
    .then(r => {
        console.log("Postres (RDS) rows returned:", r.rows.length);
        console.log("Postres (RDS) query duration:", r.duration + 'ms\n');
        return localdb.result('select * from logs')
            .then(r => {
                console.log("Postgres (Local) rows returned:", r.rows.length);
                console.log("Postgres (Local) query duration:", r.duration + 'ms\n');
            })
    })
    .catch(error=> {
        console.log(error);
    });

The advantage of using method result for benchmarks is that pg-promise automatically extends the Result with property duration.