pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfast case-insensitive sort
6+ messages / 3 participants
[nested] [flat]
* fast case-insensitive sort
@ 2003-04-15 00:49 Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Sigi Jekabsons @ 2003-04-15 00:49 UTC (permalink / raw)
To: pgsql-sql
I'm having trouble getting postgres to use an index when doing an ORDER BY
UPPER(surname), for instance. I can create an index on UPPER(surname), but
it doesn't use it in the query - is there a better way of doing a fast case
insensitive sort?
This is the query plan of ORDER BY surname, which uses the index I've
created on surname:
asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY surname;
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------------------
Index Scan using cands_surname_idx on cands (cost=0.00..3983.55 rows=31348
width=1971) (actual time=0.27..647.09 rows=31348 loops=1)
Total runtime: 664.62 msec
(2 rows)
Note the time taken.
This is the query plan of sorting by UPPER(surname) before and after
creating the index:
asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY UPPER(surname);
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
Sort (cost=39701.56..39779.93 rows=31348 width=1971) (actual
time=3384.08..3444.77 rows=31348 loops=1)
Sort Key: upper((surname)::text)
-> Seq Scan on cands (cost=0.00..1165.48 rows=31348 width=1971) (actual
time=0.06..628.09 rows=31348 loops=1)
Total runtime: 3875.90 msec
(4 rows)
asp_employ=# CREATE INDEX cand_sur_up_idx ON cands (UPPER(surname));
CREATE INDEX
asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY UPPER(surname);
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
Sort (cost=39701.56..39779.93 rows=31348 width=1971) (actual
time=3368.43..3429.51 rows=31348 loops=1)
Sort Key: upper((surname)::text)
-> Seq Scan on cands (cost=0.00..1165.48 rows=31348 width=1971) (actual
time=0.07..621.78 rows=31348 loops=1)
Total runtime: 3859.33 msec
(4 rows)
The Seq Scan is much slower. It may be interesting to note that this uses
the index:
asp_employ=# explain analyse select * from cands where
upper(surname)='SMITH';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------
Index Scan using cand_sur_up_idx on cands (cost=0.00..578.66 rows=157
width=1972) (actual time=54.52..60.06 rows=224 loops=1)
Index Cond: (upper((surname)::text) = 'SMITH'::text)
Total runtime: 60.36 msec
(3 rows)
I created a thread on this topic on the Ars Technica forums, but there was
not a solution - the discussion for reference only is here:
http://arstechnica.infopop.net/OpenTopic/page?a=tpc&s=50009562&f=6330927813;
m=1070953065
So should I be using a specific type of index, or is there a better way?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fast case-insensitive sort
@ 2003-04-15 02:21 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-04-15 02:21 UTC (permalink / raw)
To: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>; +Cc: pgsql-sql
On Tue, 15 Apr 2003, Sigi Jekabsons wrote:
> I'm having trouble getting postgres to use an index when doing an ORDER BY
> UPPER(surname), for instance. I can create an index on UPPER(surname), but
> it doesn't use it in the query - is there a better way of doing a fast case
> insensitive sort?
What's the table look like (what type is surname)? I can get behavior
like this using a varchar, but it uses the index when the field is
declared as text.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fast case-insensitive sort
@ 2003-04-15 02:40 Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sigi Jekabsons @ 2003-04-15 02:40 UTC (permalink / raw)
To: pgsql-sql; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
The table 'cands' has a 'cand_id' int4 primary key, indexed and specified
unique. 'surname' is varchar(255), indexed in the manner shown on my
previous post. You're quite right, though, I just tried indexing and
sorting by a column of type text and that worked just fine, it used the
index in the order by. Why is that?
> What's the table look like (what type is surname)? I can get behavior
> like this using a varchar, but it uses the index when the field is
> declared as text.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fast case-insensitive sort
@ 2003-04-15 05:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-04-15 05:54 UTC (permalink / raw)
To: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>; +Cc: pgsql-sql@postgresql.org, "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
"Sigi Jekabsons" <sigi.j@workskillsprofessionals.com.au> writes:
> 'surname' is varchar(255), indexed in the manner shown on my
> previous post. You're quite right, though, I just tried indexing and
> sorting by a column of type text and that worked just fine, it used the
> index in the order by. Why is that?
I would have said that the presence of the implicit varchar-to-text cast
prevented the system from detecting that the expression matches the
index value. But if it's able to detect the match in one case and not
the other, that might be a garden-variety bug. Will look at it ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fast case-insensitive sort
@ 2003-04-15 06:28 Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Sigi Jekabsons @ 2003-04-15 06:28 UTC (permalink / raw)
To: pgsql-sql
Thanks for that. Is it recommended, then, that I use type of text on
columns that I intend to sort with an index on UPPER(column), or is there
something else I should be doing?
> I would have said that the presence of the implicit varchar-to-text cast
> prevented the system from detecting that the expression matches the
> index value. But if it's able to detect the match in one case and not
> the other, that might be a garden-variety bug. Will look at it ...
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fast case-insensitive sort
@ 2003-04-15 06:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-04-15 06:38 UTC (permalink / raw)
To: Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>; +Cc: pgsql-sql
"Sigi Jekabsons" <sigi.j@workskillsprofessionals.com.au> writes:
> Thanks for that. Is it recommended, then, that I use type of text on
> columns that I intend to sort with an index on UPPER(column),
That's the best immediate workaround, for sure.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-04-15 06:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-15 00:49 fast case-insensitive sort Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
2003-04-15 02:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-15 02:40 ` Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
2003-04-15 05:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-15 06:28 ` Sigi Jekabsons <sigi.j@workskillsprofessionals.com.au>
2003-04-15 06:38 ` Tom Lane <tgl@sss.pgh.pa.us>
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