Search Postgresql Archives

Select time jump after adding filter; please help me figure out what I'm doing wrong.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux