Re: speed up insert query

2 things
tr_tran_time needs to be already in 'time format'
is_ok needs to be indexed (preferably bitmapped index)

HTH/
Martin
----- Original Message -----
From: "Tom Hart" <tomhart(at)coopfed(dot)org>
To: "Postgres General List" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, November 26, 2007 5:30 PM
Subject: [GENERAL] speed up insert query

> Hey everybody. I'm trying to speed up a query (not general optimization,
> one query in particular), and I'm not sure if there's any way to get it
> to go faster.
>
> The query looks like this
>
> INSERT INTO transaction
> (
> "tr_acct_num",
> "tr_acct_typ",
> "tr_atm_rec",
> "tr_audit_seq",
> "tr_branch_cd",
> "tr_cash_amt",
> ...
> "tr_tran_time",
> "tr_trn_rev_point",
> "tr_typ",
> "tr_typ_cd",
> "atm_trn_reg_e",
> "dataset"
> )
> SELECT
> iq_numeric("tr_acct_num"),
> "tr_acct_typ",
> iq_numeric("tr_atm_rec"),
> iq_numeric("tr_audit_seq"),
> iq_numeric("tr_branch_cd"),
> iq_numeric("tr_cash_amt"),
> ...
> cast("tr_tran_time" as time),
> iq_numeric("tr_trn_rev_point"),
> iq_numeric("tr_typ"),
> iq_numeric("tr_typ_cd"),
> "atm_trn_reg_e",
> 0
>
> FROM transaction_import
> WHERE is_ok = 'TRUE'
> ;
>
> There's not a lot I seem to be able to do about the select portion of
> this query (index on is_ok, the planner didn't even want to use it), but
> is there anything I can do to speed up the import?
>
> This is the EXPLAIN ANALYZE on the query
>
> QUERY PLAN
>
> --------------------------------------------------------------------------
---------------------------------------------------
> Seq Scan on transaction_import (cost=0.00..30953.68 rows=69239
> width=434) (actual time=0.146..2974.609 rows=68913 loops=1)
> Filter: is_ok
> Total runtime: 179091.119 ms
> (3 rows)
>
> The query is inserting ~70,000 rows into a table with ~1.8 million rows
> already in it. Anybody have any idea how I can keep this query from
> taking so long?
>
> --
> Tom Hart
> IT Specialist
> Cooperative Federal
> 723 Westcott St.
> Syracuse, NY 13210
> (315) 471-1116 ext. 202
> (315) 476-0567 (fax)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>