David Greco <David_Greco@harte-hanks.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