Thread: Slow response if pulling through pgadmin

Slow response if pulling through pgadmin

From
"Mammarelli, Joanne T"
Date:

Hi – same rookie user as before.

 

We have one table

 

100,000 rows

 

80 columns

 

When we try to retrieve the data (select * from table) using pgadmin, we get a 193456 ms retrieve time.

 

When I ran a query analyze in the command prompt, we get a 316ms retrieve time.

 

.. and finally.  When we retrieve the data from the command line, we get a 5720 ms retrieve time.

 

Any idea what could be causing the retrieval time discrepancies?

 

Thanks.

 

Joanne

 

Re: Slow response if pulling through pgadmin

From
Adrian Klaver
Date:
On 11/10/2015 07:39 AM, Mammarelli, Joanne T wrote:
> Hi – same rookie user as before.
>
> We have one table
>
> 100,000 rows
>
> 80 columns
>
> When we try to retrieve the data (select * from table) using pgadmin, we
> get a 193456 ms retrieve time.

Where is the client(pgAdmin) relative to the server?

Where was the retrieve time measured?

It takes a good bit of time for a GUI to display results, so is that
what you are seeing?

>
> When I ran a query analyze in the command prompt, we get a 316ms
> retrieve time.

Where are you running from the command line?

Do you mean EXPLAIN ANALYZE <some query> or something else?

Also can you post what the results are of whatever you did?

>
> .. and finally.  When we retrieve the data from the command line, we get
> a 5720 ms retrieve time.

>
> Any idea what could be causing the retrieval time discrepancies?

No GUI, just text.
>
> Thanks.
>
> Joanne
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Slow response if pulling through pgadmin

From
Jim Nasby
Date:
On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
> Hi – same rookie user as before.
>
> We have one table
>
> 100,000 rows
>
> 80 columns
>
> When we try to retrieve the data (select * from table) using pgadmin, we
> get a 193456 ms retrieve time.
>
> When I ran a query analyze in the command prompt, we get a 316ms
> retrieve time.

You mean EXPLAIN ANALYZE?

> .. and finally.  When we retrieve the data from the command line, we get
> a 5720 ms retrieve time.

What was psql doing with the output?

Basically, pgAdmin and psql aren't meant for users to deal with huge
data sets, because humans don't deal well with huge data sets.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


Re: Slow response if pulling through pgadmin

From
"Mammarelli, Joanne T"
Date:
Pgadmin is on the same server

316ms for a 'query analyze select * from table' when run from the command line (same directory psql is in)

The biggest concern is that we are hoping to hook up an external BI tool (such as QlikView, for example) and even with
QlikView(through an odbc connector) it takes 3 minutes for the data to come back. 



-----Original Message-----
From: Jim Nasby [mailto:Jim.Nasby@BlueTreble.com]
Sent: Tuesday, November 10, 2015 11:08 AM
To: Mammarelli, Joanne T; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow response if pulling through pgadmin

On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
> Hi - same rookie user as before.
>
> We have one table
>
> 100,000 rows
>
> 80 columns
>
> When we try to retrieve the data (select * from table) using pgadmin,
> we get a 193456 ms retrieve time.
>
> When I ran a query analyze in the command prompt, we get a 316ms
> retrieve time.

You mean EXPLAIN ANALYZE?

> .. and finally.  When we retrieve the data from the command line, we
> get a 5720 ms retrieve time.

What was psql doing with the output?

Basically, pgAdmin and psql aren't meant for users to deal with huge data sets, because humans don't deal well with
hugedata sets. 
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL
Datain Trouble? Get it in Treble! http://BlueTreble.com 


Re: Slow response if pulling through pgadmin

From
Melvin Davidson
Date:
I would also like to add that I am very suspicious of a table with 80 columns.
Offhand, it sounds like poor database design where someone was trying
to put all the eggs in one basket (figuratively).

Further, what was the exact query?
Queries of the form SELECT * will always be inherently slow with
tables that have many columns. Ideally, you only want to select just
the columns that are needed.

On 11/10/15, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
>> Hi – same rookie user as before.
>>
>> We have one table
>>
>> 100,000 rows
>>
>> 80 columns
>>
>> When we try to retrieve the data (select * from table) using pgadmin, we
>> get a 193456 ms retrieve time.
>>
>> When I ran a query analyze in the command prompt, we get a 316ms
>> retrieve time.
>
> You mean EXPLAIN ANALYZE?
>
>> .. and finally.  When we retrieve the data from the command line, we get
>> a 5720 ms retrieve time.
>
> What was psql doing with the output?
>
> Basically, pgAdmin and psql aren't meant for users to deal with huge
> data sets, because humans don't deal well with huge data sets.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


Re: Slow response if pulling through pgadmin

From
"Mammarelli, Joanne T"
Date:
Ok . thanks .. this is our first foray into postgresql and we were  comparing retrieve times based on the 'normal'
MicrosoftSQL environment to ensure that we were at least functioning properly.
 

Long term, our goal is to restructure that table into a star-schema/mdm type format to ease the maintenance.

In your opinion, would we see the benefit of moving to a postgresql environment by restructuring the 80 column table
intoa star-schema?
 

Joanne


-----Original Message-----
From: Melvin Davidson [mailto:melvin6925@gmail.com] 
Sent: Tuesday, November 10, 2015 11:16 AM
To: Jim Nasby
Cc: Mammarelli, Joanne T; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow response if pulling through pgadmin

I would also like to add that I am very suspicious of a table with 80 columns.
Offhand, it sounds like poor database design where someone was trying to put all the eggs in one basket
(figuratively).

Further, what was the exact query?
Queries of the form SELECT * will always be inherently slow with tables that have many columns. Ideally, you only want
toselect just the columns that are needed.
 

On 11/10/15, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
>> Hi – same rookie user as before.
>>
>> We have one table
>>
>> 100,000 rows
>>
>> 80 columns
>>
>> When we try to retrieve the data (select * from table) using pgadmin, 
>> we get a 193456 ms retrieve time.
>>
>> When I ran a query analyze in the command prompt, we get a 316ms 
>> retrieve time.
>
> You mean EXPLAIN ANALYZE?
>
>> .. and finally.  When we retrieve the data from the command line, we 
>> get a 5720 ms retrieve time.
>
> What was psql doing with the output?
>
> Basically, pgAdmin and psql aren't meant for users to deal with huge 
> data sets, because humans don't deal well with huge data sets.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts 
> in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it 
> in Treble! http://BlueTreble.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To 
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you wish to share my fantasy is entirely up to you.

Re: Slow response if pulling through pgadmin

From
Adrian Klaver
Date:
On 11/10/2015 08:11 AM, Mammarelli, Joanne T wrote:
> Pgadmin is on the same server
>
> 316ms for a 'query analyze select * from table' when run from the command line (same directory psql is in)

I know of no such command 'query analyze..' in Postgres.

So are you using some other tool?

Or are you actually doing 'EXPLAIN ANALYZE ..'?

When you say psql are you referring to the command line client psql or
the Postgres server?


>
> The biggest concern is that we are hoping to hook up an external BI tool (such as QlikView, for example) and even
withQlikView (through an odbc connector) it takes 3 minutes for the data to come back. 
>
>
>
> -----Original Message-----
> From: Jim Nasby [mailto:Jim.Nasby@BlueTreble.com]
> Sent: Tuesday, November 10, 2015 11:08 AM
> To: Mammarelli, Joanne T; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow response if pulling through pgadmin
>
> On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
>> Hi - same rookie user as before.
>>
>> We have one table
>>
>> 100,000 rows
>>
>> 80 columns
>>
>> When we try to retrieve the data (select * from table) using pgadmin,
>> we get a 193456 ms retrieve time.
>>
>> When I ran a query analyze in the command prompt, we get a 316ms
>> retrieve time.
>
> You mean EXPLAIN ANALYZE?
>
>> .. and finally.  When we retrieve the data from the command line, we
>> get a 5720 ms retrieve time.
>
> What was psql doing with the output?
>
> Basically, pgAdmin and psql aren't meant for users to deal with huge data sets, because humans don't deal well with
hugedata sets. 
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL
Datain Trouble? Get it in Treble! http://BlueTreble.com 
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Slow response if pulling through pgadmin

From
Adrian Klaver
Date:
On 11/10/2015 08:20 AM, Mammarelli, Joanne T wrote:
> Ok . thanks .. this is our first foray into postgresql and we were  comparing retrieve times based on the 'normal'
MicrosoftSQL environment to ensure that we were at least functioning properly. 

So what are 'normal times' and where and in what program are they being
measured?

>
> Long term, our goal is to restructure that table into a star-schema/mdm type format to ease the maintenance.
>
> In your opinion, would we see the benefit of moving to a postgresql environment by restructuring the 80 column table
intoa star-schema? 
>
> Joanne
>
>
> -----Original Message-----
> From: Melvin Davidson [mailto:melvin6925@gmail.com]
> Sent: Tuesday, November 10, 2015 11:16 AM
> To: Jim Nasby
> Cc: Mammarelli, Joanne T; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow response if pulling through pgadmin
>
> I would also like to add that I am very suspicious of a table with 80 columns.
> Offhand, it sounds like poor database design where someone was trying to put all the eggs in one basket
(figuratively).
>
> Further, what was the exact query?
> Queries of the form SELECT * will always be inherently slow with tables that have many columns. Ideally, you only
wantto select just the columns that are needed. 
>
> On 11/10/15, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> On 11/10/15 9:39 AM, Mammarelli, Joanne T wrote:
>>> Hi – same rookie user as before.
>>>
>>> We have one table
>>>
>>> 100,000 rows
>>>
>>> 80 columns
>>>
>>> When we try to retrieve the data (select * from table) using pgadmin,
>>> we get a 193456 ms retrieve time.
>>>
>>> When I ran a query analyze in the command prompt, we get a 316ms
>>> retrieve time.
>>
>> You mean EXPLAIN ANALYZE?
>>
>>> .. and finally.  When we retrieve the data from the command line, we
>>> get a 5720 ms retrieve time.
>>
>> What was psql doing with the output?
>>
>> Basically, pgAdmin and psql aren't meant for users to deal with huge
>> data sets, because humans don't deal well with huge data sets.
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts
>> in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it
>> in Treble! http://BlueTreble.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
>> make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you wish to share my fantasy is entirely up to you.
>


--
Adrian Klaver
adrian.klaver@aklaver.com