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

Don't forget to CC: the list.

Andrew Edson wrote:
> I apologize; You are correct in that I mistyped my original structure.  Here is the information for the correct
explainand explain analyze statements. 
>
>   attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A';

No need for the simple explain - explain analyse includes all the
information.

>   attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A';
>                                                                    QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on ptrans  (cost=1223.86..149853.23 rows=85741 width=21) (actual time=2302.363..70321.838
rows=6701655loops=1) 
>    ->  Bitmap Index Scan on ptrans_cid_trandt_idx  (cost=0.00..1223.86 rows=85741 width=0) (actual
time=2269.064..2269.064rows=204855 loops=1) 
>  Total runtime: 89854.843 ms

Well, it's taking 90 seconds to return 6.7 million rows. Depending on
your system and memory settings, that might not be unreasonable.

It *is* getting the estimate of returned rows wrong (it thinks 85,741
will match) which is hugely out of line. Is there something odd with
this table/column or haven't you analysed recently? How many unique
values does rcrd_cd have, and how many rows does the table have?

I don't know that you'll get this down to sub-second responses though,
not if you're trying to return 6 million rows from an even larger table.

--
   Richard Huxton
   Archonet Ltd