Re: speed up insert query

Martin Gainty wrote:

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

HTH/
Martin

The data is COPY'ed from csv's that our internal software creates, and
we don't have control over output format. Is coaxing tr_tran_time into
proper time format on the _import table going to be less costly than
doing it on the fly in the query? Also, there are a couple more casts in
the query (as date). Are casts extremely costly?

The iq_numeric function uses regex to determine whether to return a
number or null. How costly are regex based functions used like this? I
can't see it being more efficient to edit this data while it's in a
table with all text fields, no key (have to permit duplicates at this
stage), and as of yet no indexes. (As I said I tried an index on is_ok,
both a btree and a hash, and the planner seems completely uninterested).

Also, I'm sure you've heard this, but the date on your email client is
drastically wrong. I appreciate your assistance but I can only imagine
that there are quite a few people missing your good advice because
they're not looking through the new posts from 2000.

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'
;

--

Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)

Tom Hart wrote:

Martin Gainty wrote:

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

HTH/
Martin

The data is COPY'ed from csv's that our internal software creates, and
we don't have control over output format. Is coaxing tr_tran_time into
proper time format on the _import table going to be less costly than
doing it on the fly in the query? Also, there are a couple more casts
in the query (as date). Are casts extremely costly?

The iq_numeric function uses regex to determine whether to return a
number or null. How costly are regex based functions used like this? I
can't see it being more efficient to edit this data while it's in a
table with all text fields, no key (have to permit duplicates at this
stage), and as of yet no indexes. (As I said I tried an index on
is_ok, both a btree and a hash, and the planner seems completely
uninterested).

Also, I'm sure you've heard this, but the date on your email client is
drastically wrong. I appreciate your assistance but I can only imagine
that there are quite a few people missing your good advice because
they're not looking through the new posts from 2000.

I found at least one major optimization (or rather de-optimization
already in place, if the english language doesn't mind being flexed a
bit). My supervisor was playing with indexes on the rather large
transaction table. It turns out he had two multi-column indexes that
were composed of fields that were already indexed. These two indexes
didn't seem to be helping queries against the table much, but removing
just those two (there are still 4 or 5 single column indexes) cut my
execution time by 70%. That brings it to a much more manageable amount
of time.

Thanks for your reply. Hope this helps somebody else :-)

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'
;

--
Tom Hart
IT Specialist
Cooperative Federal
723 Westcott St.
Syracuse, NY 13210
(315) 471-1116 ext. 202
(315) 476-0567 (fax)