pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Dave Dutcher <dave@tridecap.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow Query on Postgres 8.2
Date: Thu, 4 Jan 2007 19:51:16 -0600
Message-ID: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com> (raw)
In-Reply-To: <7598.1167959569@sss.pgh.pa.us>

> -----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)




view thread (7+ messages)  latest in thread

Message-ID: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com>
Permalink:  ../007901c7306b$fd1d2c90$8300a8c0@tridecap.com/
Also on:    postgresql.org/message-id/007901c7306b$fd1d2c90$8300a8c0@tridecap.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dave@tridecap.com, tgl@sss.pgh.pa.us
  Subject: Re: Slow Query on Postgres 8.2
  In-Reply-To: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox