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