Thread

1. ORDER BY, LIKE !!? (* - new information)

   rex <rex@berg.dnttm.ro> — 1998-09-18T12:30:53Z

   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
   
   
   
   
   

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

   David Hartwig <daveh@insightdist.com> — 1998-09-18T13:34:30Z

   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