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
