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)