Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8E5D59FA433 for ; Thu, 4 Jan 2007 21:51:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 44266-06 for ; Thu, 4 Jan 2007 21:51:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from globalrelay.com (mail1.globalrelay.com [204.92.49.200]) by postgresql.org (Postfix) with ESMTP id B8B2B9FA0B7 for ; Thu, 4 Jan 2007 21:51:18 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [71.215.208.64] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 121078790; Thu, 04 Jan 2007 17:51:46 -0800 From: "Dave Dutcher" To: "'Tom Lane'" Cc: Subject: Re: Slow Query on Postgres 8.2 Date: Thu, 4 Jan 2007 19:51:16 -0600 Message-ID: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Importance: Normal In-Reply-To: <7598.1167959569@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/64 X-Sequence-Number: 22665 > -----Original Message----- > From: pgsql-performance-owner@postgresql.org=20 > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane >=20 > Um ... what indexes has this table got exactly? It's very=20 > unclear what > alternatives the planner is being faced with. >=20 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 =3D 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)