pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery slows down drastically with increased number of fields
6+ messages / 4 participants
[nested] [flat]
* query slows down drastically with increased number of fields
@ 2006-10-25 17:20 Tom Darci <tom@nuws.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Darci @ 2006-10-25 17:20 UTC (permalink / raw)
To: pgsql-performance
Hello All-
We have a question about numbers of fields in the select clause of a
query and how that affects query speed.
The following query simply selects the primary key field from a table
with 100,000 records:
------------------------------------------------------------
select p.opid
FROM
ott_op p
------------------------------------------------------------
It runs in about half a second (running in PgAdmin... the query run
time, not the data retrieval time)
When we change it by adding fields to the select list, it slows down
drastically. This version takes about 3 seconds:
------------------------------------------------------------
select p.opid, p.opid, p.opid, p.opid, p.opid, p.opid, p.opid, p.opid,
p.opid, p.opid, p.opid
FROM
ott_op p
------------------------------------------------------------
The more fields we add, the slower it gets.
My guess is that we are missing a configuration setting... any ideas?
Any help much appreciated.
Thanks,
-Tom
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query slows down drastically with increased number of fields
@ 2006-10-26 21:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Darci <tom@nuws.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-10-26 21:53 UTC (permalink / raw)
To: Tom Darci <tom@nuws.com>; +Cc: pgsql-performance
"Tom Darci" <tom@nuws.com> writes:
> It runs in about half a second (running in PgAdmin... the query run
> time, not the data retrieval time)
I don't have a lot of faith in PgAdmin's ability to distinguish the two.
In fact, for a query such as you have here that's just a bare seqscan,
it's arguably *all* data retrieval time --- the backend will start
emitting records almost instantly.
FWIW, in attempting to duplicate your test I get
regression=# explain analyze select f1 from foo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1541.00 rows=100000 width=4) (actual time=0.161..487.192 rows=100000 loops=1)
Total runtime: 865.454 ms
(2 rows)
regression=# explain analyze select f1,f1,f1,f1,f1,f1,f1,f1,f1,f1,f1 from foo;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1541.00 rows=100000 width=4) (actual time=0.169..603.795 rows=100000 loops=1)
Total runtime: 984.124 ms
(2 rows)
Note that this test doesn't perform conversion of the field values to
text form, so it's an underestimate of the total time spent by the
backend for the real query. But I think almost certainly, your speed
difference is all about having to send more values to the client.
The costs not measured by the explain-analyze scenario would scale darn
near linearly with the number of repetitions of f1.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query slows down drastically with increased number of fields
@ 2006-10-26 22:03 George Pavlov <gpavlov@mynewplace.com>
parent: Tom Darci <tom@nuws.com>
1 sibling, 1 reply; 6+ messages in thread
From: George Pavlov @ 2006-10-26 22:03 UTC (permalink / raw)
To: Tom Darci <tom@nuws.com>; pgsql-performance
i have wondered myself. i wouldn't do it through pgAdmin (not sure what
the best test it, but i thought psql from the same machine might be
better--see below). anyway, the funny thing is that if you concatenate
them the time drops:
~% time psql -dXXX -hYYY -UZZZ -c"select consumer_id from consumer" -o
/dev/null
psql -dXXX -hYYY -UZZZ -c"select consumer_id from consumer" -o 0.09s
user 0.01s system 29% cpu 0.341 total
~% time psql -dXXX -hstgdb0 -p5432 -Umnp -c"select
consumer_id,consumer_id,consumer_id,consumer_id,consumer_id,consumer_id,
consumer_id,consumer_id from consumer" -o /dev/null
psql -dXXX -hYYY -UZZZ -o /dev/null 0.76s user 0.06s system 45% cpu
1.796 total
~% time psql -dXXX -hYYY -UZZZ -c"select
consumer_id||consumer_id||consumer_id||consumer_id||consumer_id||consume
r_id||consumer_id||consumer_id from consumer" -o /dev/null
psql -dXXX -hYYY -UZZZ -o /dev/null 0.18s user 0.04s system 20% cpu
1.061 total
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Darci
> Sent: Wednesday, October 25, 2006 10:21 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] query slows down drastically with
> increased number of fields
>
> Hello All-
>
> We have a question about numbers of fields in the select
> clause of a query and how that affects query speed.
> The following query simply selects the primary key field
> from a table with 100,000 records:
>
> ------------------------------------------------------------
> select p.opid
> FROM
> ott_op p
>
> ------------------------------------------------------------
>
> It runs in about half a second (running in PgAdmin... the
> query run time, not the data retrieval time)
>
> When we change it by adding fields to the select list, it
> slows down drastically. This version takes about 3 seconds:
>
> ------------------------------------------------------------
> select p.opid, p.opid, p.opid, p.opid, p.opid, p.opid,
> p.opid, p.opid, p.opid, p.opid, p.opid
> FROM
> ott_op p
>
> ------------------------------------------------------------
>
> The more fields we add, the slower it gets.
>
> My guess is that we are missing a configuration setting...
> any ideas?
> Any help much appreciated.
>
> Thanks,
> -Tom
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query slows down drastically with increased number of fields
@ 2006-10-26 22:36 Jim C. Nasby <jim@nasby.net>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-10-26 22:36 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Tom Darci <tom@nuws.com>; pgsql-performance
On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote:
> i have wondered myself. i wouldn't do it through pgAdmin (not sure what
> the best test it, but i thought psql from the same machine might be
> better--see below). anyway, the funny thing is that if you concatenate
> them the time drops:
Sure. Take a look at the output and you'll see there's less data to
shove around.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query slows down drastically with increased number of fields
@ 2006-10-26 22:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-10-26 22:50 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: George Pavlov <gpavlov@mynewplace.com>; Tom Darci <tom@nuws.com>; pgsql-performance
"Jim C. Nasby" <jim@nasby.net> writes:
> On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote:
>> anyway, the funny thing is that if you concatenate
>> them the time drops:
> Sure. Take a look at the output and you'll see there's less data to
> shove around.
Even more to the point, psql's time to format its standard ASCII-art
output is proportional to the number of columns, because it has to
determine how wide to make each one ... if you used one of the other
display formats such as "expanded" or "unaligned" mode, there's probably
be less difference.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query slows down drastically with increased number of
@ 2006-10-27 00:01 Tom Darci <tom@nuws.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Darci @ 2006-10-27 00:01 UTC (permalink / raw)
To: pgsql-performance; tgl@sss.pgh.pa.us
Thanks for all the feedback, folks.
Running explain analyze (see below) I get results similar to Tom Lane,
where the 2 queries run at the same speed.
And running in psql (see below) we see the expected speed degradation
for multiple fields, although concatenation is not getting us any
advantage.
----------------------------------------------------------------------
=== RUNNING EXPLAIN ANALYZE ===
----------------------------------------------------------------------
ot6_tdarci=# explain analyze select p.opid from ott_op p;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
Seq Scan on ott_op p (cost=100000000.00..100002654.44 rows=114344
width=4) (actual time=0.008..260.739 rows=114344 loops=1)
Total runtime: 472.833 ms
Time: 473.240 ms
ot6_tdarci=# explain analyze select p.opid, p.opid, p.opid, p.opid,
p.opid, p.opid, p.opid, p.opid, p.opid, p.opid from ott_op p;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------
Seq Scan on ott_op p (cost=100000000.00..100002654.44 rows=114344
width=4) (actual time=0.006..260.795 rows=114344 loops=1)
Total runtime: 472.980 ms
Time: 473.439 ms
----------------------------------------------------------------------
=== RUNNING THE QUERIES ===
----------------------------------------------------------------------
ot6_tdarci=# \o /dev/null
ot6_tdarci=# select p.opid from ott_op p;
Time: 157.419 ms
ot6_tdarci=# select p.opid, p.opid, p.opid, p.opid, p.opid, p.opid,
p.opid, p.opid, p.opid, p.opid from ott_op p;
Time: 659.505 ms
ot6_tdarci=# select p.opid || p.opid || p.opid || p.opid || p.opid ||
p.opid || p.opid || p.opid || p.opid || p.opid from ott_op p;
Time: 672.113 ms
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 26, 2006 2:53 PM
To: Tom Darci
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query slows down drastically with increased
number of fields
"Tom Darci" <tom@nuws.com> writes:
> It runs in about half a second (running in PgAdmin... the query run
> time, not the data retrieval time)
I don't have a lot of faith in PgAdmin's ability to distinguish the two.
In fact, for a query such as you have here that's just a bare seqscan,
it's arguably *all* data retrieval time --- the backend will start
emitting records almost instantly.
FWIW, in attempting to duplicate your test I get
regression=# explain analyze select f1 from foo;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------
Seq Scan on foo (cost=0.00..1541.00 rows=100000 width=4) (actual
time=0.161..487.192 rows=100000 loops=1) Total runtime: 865.454 ms
(2 rows)
regression=# explain analyze select f1,f1,f1,f1,f1,f1,f1,f1,f1,f1,f1
from foo;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------
Seq Scan on foo (cost=0.00..1541.00 rows=100000 width=4) (actual
time=0.169..603.795 rows=100000 loops=1) Total runtime: 984.124 ms
(2 rows)
Note that this test doesn't perform conversion of the field values to
text form, so it's an underestimate of the total time spent by the
backend for the real query. But I think almost certainly, your speed
difference is all about having to send more values to the client.
The costs not measured by the explain-analyze scenario would scale darn
near linearly with the number of repetitions of f1.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-10-27 00:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-25 17:20 query slows down drastically with increased number of fields Tom Darci <tom@nuws.com>
2006-10-26 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-26 22:03 ` George Pavlov <gpavlov@mynewplace.com>
2006-10-26 22:36 ` Jim C. Nasby <jim@nasby.net>
2006-10-26 22:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-27 00:01 Re: query slows down drastically with increased number of Tom Darci <tom@nuws.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox