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

Re: Real vs Int performance

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "David Greco" <David_Greco(at)harte-hanks(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Real vs Int performance
Date: 2011-01-26 21:52:51
Message-ID: 4D4043530200002500039D99@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
David Greco <David_Greco(at)harte-hanks(dot)com> wrote:
 
> If I change this field from an integer to a real, I get about a
> 70x increase in performance of the query.
 
> I wished to simplify things a bit here (and don't yet know how to
> EXPLAIN ANALYZE a parameterized query).
 
>  carrier_source_id | integer                     |
 
> runtime: 0.108 ms
 
>  carrier_source_id | real                        |
 
> runtime: 0.097 ms
 
This doesn't show the problem, so it's hard to guess the cause. 
Perhaps you can do it with a prepared statement?:
 
http://www.postgresql.org/docs/9.0/interactive/sql-prepare.html
 
Also, plans can be completely different based on the number of rows,
width of the rows, distribution of values, etc.  You may want to
select against the actual tables where you've seen the problem.
 
One tip -- if size permits, try to CLUSTER both tables to avoid any
bloat issues, and VACUUM ANALYZE the tables to ensure that hint bits
are set and statistics are up to date before running the tests.  Run
each test several times in a row to see what affect caching has on
the issue.
 
-Kevin

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2011-01-26 22:12:12
Subject: Re: Real vs Int performance
Previous:From: David GrecoDate: 2011-01-26 19:31:58
Subject: Real vs Int performance

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