Re: simple case using index on windows but not on linux - Mailing list pgsql-performance

> Can you post EXPLAIN ANALYSE for the LIKE and <> queries that should be
> using the index? With enable_seqscan on and off please.
>

OK - I don't know what happened, but now my linux installation is
behaving like the windows one.  I honestly don't know what changed,
which I know doesn't help people determine the cause of my issue....

But I still have a problem with > and <, on both environments.

Now, both LIKE and = are using the index with no options on it.

But the other operators are not.

Firstly, with enable_seqscan on:

orderstest=# explain analyze select c_number from t_order where
c_number like '00001%';
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_order_c_number on t_order  (cost=0.00..3.01 rows=1
width=11) (actual time=0.167..0.610 rows=100 loops=1)
   Index Cond: (((c_number)::text >= '00001'::character varying) AND
((c_number)::text < '00002'::character varying))
   Filter: ((c_number)::text ~~ '00001%'::text)
 Total runtime: 0.921 ms
(4 rows)

orderstest=# explain analyze select c_number from t_order where
c_number > '0001';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on t_order  (cost=0.00..18312.50 rows=878359 width=11)
(actual time=1.102..4364.704 rows=878000 loops=1)
   Filter: ((c_number)::text > '0001'::text)
 Total runtime: 6431.968 ms
(3 rows)

And now with enable_seqscan off:

orderstest=# explain analyze select c_number from t_order where
c_number like '00001%';
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_order_c_number on t_order  (cost=0.00..3.01 rows=1
width=11) (actual time=0.245..0.674 rows=100 loops=1)
   Index Cond: (((c_number)::text >= '00001'::character varying) AND
((c_number)::text < '00002'::character varying))
   Filter: ((c_number)::text ~~ '00001%'::text)
 Total runtime: 0.971 ms
(4 rows)

(Just the same)

orderstest=# explain analyze select c_number from t_order where
c_number > '0001';
                                                                 QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_order_c_number on t_order  (cost=0.00..22087.31
rows=878912 width=11) (actual time=0.230..3504.909 rows=878000
loops=1)
   Index Cond: ((c_number)::text > '0001'::text)
 Total runtime: 5425.931 ms
(3 rows)

(Now using the index but getting awful performance out of it - how's that?)

The difference seems to be whether it is treating the index condition
as 'character varying' or 'text'.

Basically, can I do > < >= <= on a varchar without causing a seq-scan?

--
Simon Godden