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
