SQL Performance, What executes faster in the SELECT and WHERE conditions

UPDATE: Crap! it's not an integer it's character varying(10)

Executing the query like this uses the index

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" = '0123456789'

But does not use the index if I execute this

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" LIKE '01%'

or this

SELECT t."FieldID"
FROM table t
WHERE "substring"(t."FieldID", 0, 3) = '01'

also this

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" ~ '^01'

My index looks like this

CREATE UNIQUE INDEX fieldid_index
  ON "table"
  USING btree
  ("FieldID");

Running PostgreSQL 7.4 (Yep Upgrading)

I'm optimizing my query and wanted to know if there is any performance gains using one of the three types of expressions in either the SELEC开发者_C百科T or WHERE clause in the statement.

NOTE: The query that executes with these style of constraints returns around 200,000 records

Example Data is a character varying(10): 0123456789 and it is indexed as well

1. (Substring)

SELECT CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

2. (Like)

SELECT CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

3. (RegEx)

SELECT CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

Also is there any performance advantages using one over the other in the WHERE clause?

1. (Substring)

WHERE CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END = 1

2. (Like)

WHERE CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 1
    ELSE 0
END = 1

3. (RegEx)

WHERE CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 1
    ELSE 0
END = 1

Would using one option in the SELECT and a different option in the WHERE clause improve performance?

Personally I think that someone who creates this kind of a problem should not be allowed to use the word "performance". Restrictions (like those in the WHERE clause) on the text representation of the contents of a numeric field (maybe even a keyfield) indicate bad design, IMHO.

If this were my data, I would add a flagfield to the record, indicating wanted / not wanted in query xyz. One could even put it into a separate table. I prefer adding a (redundant?) column to creating an entire index based on GW-basic-substring rubbish.

The two things that have the most effect are indexing and sargability. Sargability means using an expression that can take advantage of an index. You measure their effect by using

ANALYZE your_first_table;
-- ANALYZE other tables used in this query.
EXPLAIN ANALYZE
SELECT ...

See the docs for Examining index usage.

You might be able to take advantage of indexes on expressions or partial indexes. PostgreSQL 7.4 supports both indexes on expressions and partial indexes. For testing, you can discourage certain kinds of query plans. (Also in 7.4.)

An expression-based index that might work for you:

create index firsttwochars
on your-table-name (substring(your-column-name from 1 for 2));

But you still need to test your queries to see whether they actually use the index. (Whether they're sargable.) This one might work.

select your-column-name 
from your-table-name 
where substring(your-column-name from 1 for 2) = '01'

Query plan without the index on the first two characters. (My test table uses random text-only usernames, which is why I searched on 'ab' instead of '01'.)

Seq Scan on substring  (cost=0.00..205.00 rows=50 width=11) (actual time=0.315..4.377 rows=14 loops=1)
  Filter: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 4.414 ms

Query plan with the index on the first two characters.

Bitmap Heap Scan on substring  (cost=4.36..37.61 rows=14 width=11) (actual time=0.036..0.056 rows=14 loops=1)
  Recheck Cond: (substring((username)::text, 1, 2) = 'ab'::text)
  ->  Bitmap Index Scan on firsttwochars  (cost=0.00..4.36 rows=14 width=0) (actual time=0.028..0.028 rows=14 loops=1)
        Index Cond: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 0.098 ms

In SQL Server the version with LIKE '01%' would be sargable. It actually converts these LIKE queries without leading wildcards to range queries.

The execution plan shows the seek predicate as being YourCol >= '01' AND YourCol < '02' perhaps a similar sort of rewrite could help in Postgresql?

In the select list, there will probably not be much difference between the three expressions. It's all CPU time.

For the WHERE clause, you could add an expression index such as

CREATE INDEX foo ON sometable ((
CASE
    WHEN "substring"("FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END
));

but the selectivity of such a Boolean index will likely be bad enough to not interest the planner. It would be better to rewrite the WHERE clause to just

WHERE "substring"("FieldID"::text, 0, 3) = '01'::text

and then index that.

For the LIKE and regex cases you could consider a text_pattern_ops index as well; see the documentation.

All in all, I think you have some cleanup work to do on that query.