Thread: 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

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
>

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)

"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