pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
"SELECT" problem on 7.0.3
2+ messages / 2 participants
[nested] [flat]

* "SELECT" problem on 7.0.3
@ 2001-01-11 17:39  George Moga <george@cicnet.ro>
  0 siblings, 1 reply; 2+ messages in thread

From: George Moga @ 2001-01-11 17:39 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I use:
agro=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3

compiled with "--enable-locales --enable-encoding --with-tcl" options on
Mandrake LINUX 7.2 with 2.2.17-21mdk kernel.

I have BIG problems when I want to do the following "select":

agro=# SELECT * FROM conturi WHERE id ~* '^1:1:[0-9]*:$' ORDER BY id;

The conditions are the following:

Table "conturi" has the following structure:

agro=# \d conturi
          Table "conturi"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 id        | varchar(32) | not null
 cheie     | varchar(32) |
 denumire  | varchar(60) | not null
 tip       | char(1)     | not null
 functie   | char(1)     |
 cc        | integer     |
Index: conturi_id

I use the unique index conturi_id on field id.

agro=# SELECT * FROM conturi ORDER BY id LIMIT 20;
    id    | cheie |                           denumire
| tip | functie | cc
----------+-------+--------------------------------------------------------------+-----+---------+----

 1:       | 1     | Capitaluri
| F   | P       |
 1:0:     | 10    | Capital si rezerve
| F   | P       |
 1:0:1:   | 101   | Capital social
| F   | P       |
 1:0:1:1: | 1011  | Capital subscris nevarsat
| O   | P       |
 1:0:1:2: | 1012  | Capital subscris varsat
| O   | P       |
 1:0:1:7: | 1017  | Capital social reevaluat
| O   | P       |
 1:0:4:   | 104   | Prime legate de capital
| F   | P       |
 1:0:4:1: | 1041  | Prime de emisiune sau de aport
| O   | P       |
 1:0:4:2: | 1042  | Prime de fuziune
| O   | P       |
 1:0:5:   | 105   | Diferente din reevaluare
| F   | B       |
 1:0:5:3: | 1053  | Diferente din reevaluari cladiri
| O   | B       |
 1:0:5:4: | 1054  | Diferente din reevaluari constructii speciale
| O   | B       |
 1:0:5:6: | 1056  | Diferente din reevaluarea amortizarii la cladiri
| O   | B       |
 1:0:5:7: | 1057  | Diferente din reevaluarea amortizarii la constructii special
| O   | B       |
 1:0:6:   | 106   | Rezerve
| F   | P       |
 1:0:6:1: | 1061  | Rezerve legale
| O   | P       |
 1:0:6:3: | 1063  | Rezerve statutare
| O   | P       |
 1:0:6:8: | 1068  | Alte rezerve
| O   | P       |
 1:0:7:   | 107   | Rezultatul reportat
| O   | P       |
 1:0:8:   | 108   | Contul intreprinzatorului individual
| F   | P       |
(20 rows)

Realy I have:

agro=# SELECT count(*) FROM conturi;
 count
-------
  2690
(1 row)

rows in table.

(VACUUM ... VACUUM ANALYZE was used before I do this example)

The "id" field define a tree structure and, if I want to find all children of
"1:0:", I do:

agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id;
 id | cheie | denumire | tip | functie | cc
----+-------+----------+-----+---------+----
(0 rows)

When I drop the unique index ...

agro=# DROP INDEX conturi_id;
DROP

agro=#  SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id;
   id   | cheie |               denumire               | tip | functie | cc
--------+-------+--------------------------------------+-----+---------+----
 1:0:1: | 101   | Capital social                       | F   | P       |
 1:0:4: | 104   | Prime legate de capital              | F   | P       |
 1:0:5: | 105   | Diferente din reevaluare             | F   | B       |
 1:0:6: | 106   | Rezerve                              | F   | P       |
 1:0:7: | 107   | Rezultatul reportat                  | O   | P       |
 1:0:8: | 108   | Contul intreprinzatorului individual | F   | P       |
(6 rows)

it works fine ... but if create the index again ...

agro=# CREATE UNIQUE INDEX conturi_id ON conturi(id);
CREATE

agro=# SELECT * FROM conturi WHERE id ~* '^1:0:[0-9]*:$' ORDER BY id;
 id | cheie | denumire | tip | functie | cc
----+-------+----------+-----+---------+----
(0 rows)

nothing again.

When I use the same succesion of commands on a table with the same structure but
wonly 10 rows the "SELECT" works fine with or without index.

On 6.5.3 (compiled exactly on same machine and conditions) I have no problems
with this "SELECT", it works (the biggest structure have 10.000 rows).

Any sugestions ... ???


Thanks in advance and ... sorry for my english!!

George Moga,
    Data SYSTEMS Srl
    Slobozia, ROMANIA





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: "SELECT" problem on 7.0.3
@ 2001-01-12 00:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: George Moga <george@cicnet.ro>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-01-12 00:36 UTC (permalink / raw)
  To: George Moga <george@cicnet.ro>; +Cc: pgsql-sql

What locale and encoding are you using?

Evidently the LIKE index optimization is failing, but I would
not have guessed that a string like 1:0: would cause a problem,
especially not if all the entries in the column contain just
digits and colons.

You can find plenty of material about LIKE+index problems in
the list archives.  For 7.1, we've gone so far as to disable
the optimization altogether in non-ASCII locales (and I'm sure
we'll get complaints about that, too :-().

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-01-12 00:36 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-11 17:39 "SELECT" problem on 7.0.3 George Moga <george@cicnet.ro>
2001-01-12 00:36 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox