Re: [GENERAL] ORDER BY, LIKE !!? (* - new information)

I do not recall if 6.3 can indexes with the LIKE operator. I know 6.4 does.
In any case, btree indexes are are ordered indexes and may be used when a query
is specifies with a fixed prefix (i.e. 'pattern%'). In this case an ordered
search can be performed. However, if the prefix is a wildcard (i.e. '%pattern'
or '%pattern%') then the btree is of no use. This is because all entries in
the index are possible matches to the pattern.

To illustrate my point: Try looking up a word in the dictionary when you have
no idea what the word starts with.

rex wrote:

> Hi,
> I have :
> - a table with more than 2.000.000 records. It looks like this :
> +----------------------------------+----------------------------------+-----
> --+
> | Field | Type
> |Length |
> +----------------------------------+----------------------------------+-----
> --+
> | fileno | int4
> |4 |
> | size | int4
> |4 |
> | type | char2
> |2 |
> | date | datetime
> |8 |
> | host | varchar()
> |32 |
> | name(with path) | varchar()
> |1024 |
> +----------------------------------+----------------------------------+-----
> --+
> - a PostgreSQL 6.3
> -* 4.1 AIX system, 128 RAM, 300 virtual memory, 700 M HDD, 'jfs' is the type
> HDD
> - postmaster is started with the following parameters :
> -i -B 1024 -S -o '-F -S 10240' -D/opt/pgdata
> - * an index on 'name' field (but the LIKE don't use indexes - anybody can
> obtain this result if use the EXPLAIN command. )
>
> I want to select only few (100) rows, [from a given row,] having an order
> criterium,
> faster (< 2 min) :
> " SELECT * FROM File WHERE name LIKE '%/u/projects%' [AND name LIKE ...]
> ORDER BY name;"
> [from the beginning of the row 750000]
> OR
> " SELECT * FROM File WHERE name LIKE '%/u/projects%' [AND name LIKE ...]
> ORDER BY date;"
> [from the beginning of the row 750000]
> Could someone help me ?
> *Could someone explain me what are the indexes ? They work (properly) only
> on WHERE clause with '=', '<', etc. operators ? ( The size of the index file
> in ~1/3 * (size of the table file) and if the table is ... the index is also
> ...)
>
> Thanks,
> rex