Обсуждение: Select time jump after adding filter; please help me figure out what I'm doing wrong.
Select time jump after adding filter; please help me figure out what I'm doing wrong.
От
Andrew Edson
Дата:
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.
Re: Select time jump after adding filter; please help me figure out what I'm doing wrong.
От
Richard Huxton
Дата:
Andrew Edson wrote: > The following is a copy of my index creation statement, index name, and explain and explain analyze output on the statementI 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'; >> attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = 0; >> attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = 0; The index has a where clause that doesn't match your query. It wouldn't use the index anyway - you're not filtering or sorting on it. -- Richard Huxton Archonet Ltd