Re: Less is More

[Tom Lane <tgl@xxxxxxxxxxxxx>]

Paul Ramsey <pramsey@xxxxxxxxxxxxxxxxx> writes:
> I have an odd performance quirk I wonder if anyone has any theories forâ?¦ (not a one-time result, table all heated up, etc)


> spain=# explain analyze select way from planet_osm_point; Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on planet_osm_point  (cost=0.00..37240.97 rows=1748797 width=32) (actual time=0.044..813.521 rows=1748797 loops=1)
> Â Total runtime: 902.256 ms
> (2 rows)

> Time: 902.690 ms

> spain=# explain analyze select * from planet_osm_point;
> Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on planet_osm_point  (cost=0.00..37240.97 rows=1748797 width=848) (actual time=0.051..241.413 rows=1748797 loops=1)
> Â Total runtime: 330.271 ms
> (2 rows)

> Time: 331.869 ms

"SELECT *" gets to skip the projection step that is usually needed to
remove unwanted columns from the query result.

Note that neither of these numbers have much to do with the real
time to execute the query and return results to the client.  EXPLAIN
ANALYZE doesn't bother to convert the query results to text, much
less do any network I/O.

			regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general