From: simon godden <sgodden@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: simple case using index on windows but not on linux
Date: Wed, 4 Oct 2006 15:46:12 +0100
Message-ID: <168519350610040746q64804cbcr6d73e1dd4e8592d7@mail.gmail.com> (raw)
In-Reply-To: <4523C007.4050606@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgodden@gmail.com, dev@archonet.com
Subject: Re: simple case using index on windows but not on linux
In-Reply-To: <168519350610040746q64804cbcr6d73e1dd4e8592d7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox