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