Skip site navigation (1) Skip section navigation (2)

Re: query slows down drastically with increased number of fields

From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
To: "Tom Darci" <tom(at)nuws(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: query slows down drastically with increased number of fields
Date: 2006-10-26 22:03:38
Message-ID: 8C5B026B51B6854CBE88121DBF097A8655C484@ehost010-33.exch010.intermedia.net (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org 
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Tom Darci
> Sent: Wednesday, October 25, 2006 10:21 AM
> To: pgsql-performance(at)postgresql(dot)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
> 

In response to

Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2006-10-26 22:09:37
Subject: Re: VACUUMs take twice as long across all nodes
Previous:From: Jim C. NasbyDate: 2006-10-26 21:59:36
Subject: Re: VACUUMs take twice as long across all nodes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group