I've been working on a db project intended to perform modifications to one db based on the data stored in another one. Specifically, I'm supposed to check a pair of tables based on two criteria; an id field, and a timestamp. This will be crossreferenced with the second database; the timestamps will be used to update timestamps on anything which resides in the table of interest there.
I was running the sequence through perl; with 76 records in the test copy of the second database, I was getting a four, five minute run. Not really bad, but I'm expecting the number of records in the second db to hit several thousand in production, so I thought I'd see if I could speed things up by adding an index on what I'm searching for.
After about an hour of run time, I killed the program and started looking into things.
The following is a copy of my index creation statement,
index name, and explain and explain analyze output on the statement I was trying to run. Would someone please help me figure out what I'm doing wrong here?
> attest=# create index ptrans_cid_trandt_idx on ptrans(cntrct_id, tran_dt) where rcrd_cd = '0A';
>
>
> "ptrans_cid_trandt_idx" btree (cntrct_id, tran_dt) WHERE rcrd_cd = '0A'::bpchar
>
>
> attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = 0;
> QUERY PLAN
> ----------------------------------------------------------------
> Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21)
> Filter: ((rcrd_cd)::text = '0'::text)
> (2 rows)
>
>
> attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = 0;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21) (actual time=60585.740..60585.740 rows=0 loops=1)
> Filter: ((rcrd_cd)::text = '0'::text)
> Total runtime: 60585.797 ms
> (3 rows)
>
>
> "ptrans_cid_trandt_idx" btree (cntrct_id, tran_dt) WHERE rcrd_cd = '0A'::bpchar
>
>
> attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = 0;
> QUERY PLAN
> ----------------------------------------------------------------
> Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21)
> Filter: ((rcrd_cd)::text = '0'::text)
> (2 rows)
>
>
> attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = 0;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21) (actual time=60585.740..60585.740 rows=0 loops=1)
> Filter: ((rcrd_cd)::text = '0'::text)
> Total runtime: 60585.797 ms
> (3 rows)
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
