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)
---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on,
when.
