Real vs Int performance - Mailing list pgsql-performance

New to Postgres and am prototyping a migration from Oracle to Postgres 9.0.1 on Linux. Starting with the data warehouse. Current process is to load the data from

our OLTP (Oracle), dump it into another instance of Oracle for staging and manipulation, then extract it and load it into Infobright. I am trying

to replace the Oracle instance used for staging and manipulation with Postgres. Kettle (PDI), a Java ETL tool, is used for this process.

Came across a problem I find perplexing. I recreated the dimensional tables in Oracle and the fields that are integers in Oracle became integers

in Postgres. Was experiencing terrible performance during the load and narrowed down to a particular dimensional lookup problem. The table

dim_carrier holds about 80k rows. You can see the actual query issued by Kettle below, but basically I am looking up using the business key from

our OLTP system. This field is carrier_source_id and is indexed as you can see below. If I change this field from an integer to a real, I get

about a 70x increase in performance of the query. The EXPLAIN ANALYZE output is nearly identical, except for the casting of 1 to a real when the column

is a real. In real life, this query is actually bound and parameterized, but I wished to simplify things a bit here (and don't yet know how to EXPLAIN ANALYZE a parameterized

query). Now in terms of actual performance, the same query executed about 25k times takes 7 seconds with the real column, and 500 seconds with the integer column.

What gives here? Seems like integer (or serial) is a pretty common choice for primary key columns, and therefore what I'm experiencing must be an anomoly.

                Table "hits_olap.dim_carrier"

      Column       |            Type             | Modifiers

-------------------+-----------------------------+-----------

 carrier_id        | integer                     | not null

 dim_version       | smallint                    |

 dim_effect_date   | timestamp without time zone |

 dim_expire_date   | timestamp without time zone |

 carrier_source_id | integer                     |

 carrier_name      | character varying(30)       |

 carrier_type      | character varying(30)       |

 carrier_scac      | character varying(4)        |

 carrier_currency  | character varying(3)        |

 current_row       | smallint                    | default 0

Indexes:

    "dim_carrier_pk" PRIMARY KEY, btree (carrier_id)

    "idx_dim_carrier_lookup" btree (carrier_source_id)

VACUUM

ANALYZE

REINDEX

 EXPLAIN ANALYZE SELECT CARRIER_ID, DIM_VERSION FROM HITS_OLAP.DIM_CARRIER WHERE CARRIER_SOURCE_ID = '1'  AND now() >= DIM_EFFECT_DATE

 AND now() < DIM_EXPIRE_DATE;

                                                             QUERY PLAN                                                              

-------------------------------------------------------------------------------------------------------------------------------------

 Index Scan using idx_dim_carrier_lookup on dim_carrier  (cost=0.00..12.10 rows=2 width=6) (actual time=0.076..0.077 rows=1 loops=1)

   Index Cond: (carrier_source_id = 1)

   Filter: ((now() >= dim_effect_date) AND (now() < dim_expire_date))  Total runtime: 0.108 ms

(4 rows)

ALTER TABLE

ALTER TABLE

                Table "hits_olap.dim_carrier"

      Column       |            Type             | Modifiers

-------------------+-----------------------------+-----------

 carrier_id        | integer                     | not null

 dim_version       | smallint                    |

 dim_effect_date   | timestamp without time zone |

 dim_expire_date   | timestamp without time zone |

 carrier_source_id | real                        |

 carrier_name      | character varying(30)       |

 carrier_type      | character varying(30)       |

 carrier_scac      | character varying(4)        |

 carrier_currency  | character varying(3)        |

 current_row       | smallint                    | default 0

Indexes:

    "dim_carrier_pk" PRIMARY KEY, btree (carrier_id)

    "idx_dim_carrier_lookup" btree (carrier_source_id)

VACUUM

ANALYZE

REINDEX

 EXPLAIN ANALYZE SELECT CARRIER_ID, DIM_VERSION FROM HITS_OLAP.DIM_CARRIER WHERE CARRIER_SOURCE_ID = '1'  AND now() >= DIM_EFFECT_DATE

 AND now() < DIM_EXPIRE_DATE;

                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Index Scan using idx_dim_carrier_lookup on dim_carrier  (cost=0.00..12.10 rows=2 width=6) (actual time=0.068..0.069 rows=1 loops=1)

   Index Cond: (carrier_source_id = 1::real)

   Filter: ((now() >= dim_effect_date) AND (now() < dim_expire_date))  Total runtime: 0.097 ms

(4 rows)

Thanks for the help,

Dave Greco