Re: Slow Query on Postgres 8.2 - Mailing list pgsql-performance

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
>
> Um ... what indexes has this table got exactly?  It's very
> unclear what
> alternatives the planner is being faced with.
>

Here is the table definition.  Thanks.

                           Table "public.om_transaction"
     Column      |          Type          |                Modifiers
-----------------+------------------------+---------------------------------
--------
 transaction_id  | character varying(20)  | not null default '0'::character
varying
 type            | character varying(20)  | not null default ''::character
varying
 fund_id         | character varying(10)  | not null default ''::character
varying
 owner_trader_id | character varying(10)  | not null default ''::character
varying
 strategy_id     | character varying(30)  | not null default ''::character
varying
 instrument_id   | integer                | default 0
 cf_account_id   | integer                | not null default 0
 as_of_date      | date                   | not null default
'0001-01-01'::date
 insert_date     | date                   | not null default
'0001-01-01'::date
 amount          | numeric(22,9)          | not null default 0.000000000
 currency_id     | integer                | not null default 0
 process_state   | integer                | not null
 comment         | character varying(256) | default ''::character varying
Indexes:
    "om_transaction_pkey" PRIMARY KEY, btree (transaction_id)
    "cf_account_id_om_transaction_index" btree (cf_account_id)
    "currency_id_om_transaction_index" btree (currency_id)
    "fund_id_om_transaction_index" btree (fund_id)
    "instrument_id_om_transaction_index" btree (instrument_id)
    "om_transaction_om_transaction_index" btree (as_of_date, fund_id,
strategy_id, owner_trader_id, cf_account_id, instrument_id, "type")
    "om_transaction_partial_process_state_index" btree (process_state) WHERE
process_state = 0
    "owner_trader_id_om_transaction_index" btree (owner_trader_id)
    "strategy_id_om_transaction_index" btree (strategy_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (owner_trader_id) REFERENCES om_trader(trader_id)
    "$2" FOREIGN KEY (fund_id) REFERENCES om_fund(fund_id)
    "$3" FOREIGN KEY (strategy_id) REFERENCES om_strategy(strategy_id)
    "$4" FOREIGN KEY (cf_account_id) REFERENCES om_cf_account(id)
    "$5" FOREIGN KEY (instrument_id) REFERENCES om_instrument(id)
    "$6" FOREIGN KEY (currency_id) REFERENCES om_instrument(id)