Skip site navigation (1) Skip section navigation (2)

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

From: "rex" <rex(at)berg(dot)dnttm(dot)ro>
To: <pgsql-general(at)postgreSQL(dot)org>
Subject: ORDER BY, LIKE !!? (* - new information)
Date: 1998-09-18 12:30:53
Message-ID: 01bde300$2d28ddc0$6a2ca8c0@rex.berg (view raw or flat)
Thread:
Lists: pgsql-general
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




Responses

pgsql-general by date

Next:From: David HartwigDate: 1998-09-18 13:34:30
Subject: Re: [GENERAL] ORDER BY, LIKE !!? (* - new information)
Previous:From: David HartwigDate: 1998-09-18 12:24:11
Subject: Re: [GENERAL] slow queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group