DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=JBheC6XUU1mzEa+nvuUljRIPBA8+E4QhcQwWbCqVYEPRY7BR0N9T2sHEAviY5OJsdWPXnJKb/SWwY72TwFhtMiqtoye6bUlXnLi0X0RERhIjiaU67cFt/KsrIyEM8PPrf2y5m6PCOCojdLPrGmWI9B2mUaC4sSZW99eGjW1EuUc=
Message-ID: <168519350610040746q64804cbcr6d73e1dd4e8592d7@mail.gmail.com>
Date: Wed, 4 Oct 2006 15:46:12 +0100
From: "simon godden" <sgodden@gmail.com>
To: "Richard Huxton" <dev@archonet.com>
Subject: Re: simple case using index on windows but not on linux
Cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
In-Reply-To: <4523C007.4050606@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <168519350610040048m7c4031e3t1a24b3fd4d9265a3@mail.gmail.com>
	<45237AD0.50604@enterprisedb.com>
	<168519350610040219n648a282hf159ff272e0f672a@mail.gmail.com>
	<45238085.3000407@archonet.com>
	<168519350610040240k346bd99fj9537822d79443f8c@mail.gmail.com>
	<45238F54.70609@archonet.com>
	<168519350610040635r4d041bd7vba47ca0e31a4c7d0@mail.gmail.com>
	<4523BB75.9020200@archonet.com>
	<168519350610040647v395f1aeag8e9c6825ad25320e@mail.gmail.com>
	<4523C007.4050606@archonet.com>

> 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