Re: Help on query plan.

Somebody called 'Tom Lane' tried to say something! Take a look:
>>>access=# explain analyze select * from tbl_access where ((ip >=
>>>'12'::character varying) AND (ip <> '13'::character varying))
>>>access-# ;
>>>QUERY PLAN
>>>-----------------------------------------------------------------
>>>Seq Scan on tbl_access  (cost=0.00..45504.81 rows=1193347 width=133)
>>>(actual time=59.03..84286.81 rows=1193987 loops=1)
>>>Filter: ((ip >= '12'::character varying) AND (ip <> '13'::character
>>>varying))
>>>Total runtime: 86862.12 msec
>>>(3 rows)
>
>
>>Hm, so *all* of the rows in your table have ip values starting with '12'?
>
>
> Oh, wait wait wait.  There's a typo in that explain command.  It should
> be
>
> explain analyze select * from tbl_access where ((ip >=
> '12'::character varying) AND (ip < '13'::character varying))
>
>             regards, tom lane
>
>

  Hmm ok, running it again...

explain analyze select * from tbl_access where((ip >='12'::character va
rying) AND (ip < '13'::character varying));
                                                      QUERY PLAN

--------------------------------------------------------------------------------
  Seq Scan on tbl_access  (cost=0.00..45504.81 rows=16968 width=133)
(actual time=78.64..29174.63 rows=20318 loops=1)
    Filter: ((ip >= '12'::character varying) AND (ip < '13'::character
varying))
  Total runtime: 29222.49 msec

  ====================   / ******* \   ================

   And now, the same query with enable_seqscan set to OFF.

explain analyze select * from tbl_access where((ip >='12'::character
varying) AND (ip < '13'::character varying));
                                                             QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------
  Index Scan using teste1 on tbl_access  (cost=0.00..63182.79 rows=16968
width=133) (actual time=0.91..1813.32 rows=20318 loops=1)
    Index Cond: ((ip >= '12'::character varying) AND (ip <
'13'::character varying))
  Total runtime: 1863.33 msec
(3 rows)


   Any other suggestions?

   thanks,

william

--
Perl combines all of the worst aspects of BASIC, C and line noise.
                 -- Keith Packard