Re: Real vs Int performance

From: Kevin Grittner
Subject: Re: Real vs Int performance
Date: ,
Msg-id: 4D4043530200002500039D99@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: Real vs Int performance  (David Greco)
List: pgsql-performance

Tree view

Real vs Int performance  (David Greco, )
 Re: Real vs Int performance  ("Kevin Grittner", )
 Re: Real vs Int performance  (Tom Lane, )
  Re: Real vs Int performance  (David Greco, )
   Re: Real vs Int performance  (Tom Lane, )
    Re: Real vs Int performance  (Shaun Thomas, )
     Re: Real vs Int performance  (Mladen Gogala, )
  Re: Real vs Int performance  ("Igor Neyman", )

David Greco <> 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


pgsql-performance by date:

From: "mark"
Date:
Subject: Re: Queries becoming slow under heavy load
From: Scott Marlowe
Date:
Subject: Re: FW: Queries becoming slow under heavy load