From: Joost Witteveen <joostje@komputilo.org>
To: pgsql-sql@postgresql.org
Subject: create index right after create table not working?
Date: Sun, 16 Mar 2003 00:53:32 +0100
Message-ID: <20030315235332.GA32358@co.uea.org> (raw)
It seems as though "CREATE INDEX" are failing if I give them right after
a CREATE TABLE, although they don't return any error messages.
DROP table tmp0;
CREATE TABLE tmp0(v0 varchar);
DROP index tmp0_v0_idx;
CREATE index tmp0_v0_idx ON tmp0(v0); --this one silently fails
INSERT INTO tmp0 VALUES('jwit');
SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';
As the table db is rather large (300000 entries), the select takes
several seconds, which is usual if there is no index on table tmp0,
but, if I now do again:
DROP index tmp0_v0_idx; -- drop returns no error message
CREATE index tmp0_v0_idx ON tmp0(v0); -- this one now works!
SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';
then the select returns instantly.
The strange thing is that if I do "\d tmp0", then I always see:
Table "tmp0"
Column | Type | Modifiers
--------+-------------------+-----------
v0 | character varying |
Indexes: tmp0_v0_idx
ie both after the first piece of code, and after the second, the index
seems to be there. Just the select takes ages in the first case.
Oh, and I notice that doing:
DROP table tmp0;
CREATE TABLE tmp0(v0 varchar);
DROP index tmp0_v0_idx;
INSERT INTO tmp0 VALUES('jwit'); --first insert, then
CREATE index tmp0_v0_idx ON tmp0(v0); --create (inverse of first example)
SELECT val AS v0 FROM db, tmp0 WHERE tab='pers' AND tmp0.v0=id AND var='nomfam';
ie. swapping the INSERT and CREATE index commands,
now the select returns instantly too.
Unfortunately this order is rather difficult for me to implement, and should
not be needed, or what?
Can CREATE index commands be issued right after the CREATE TABLE?
Postgresql: 7.2.1, debian release 3 and 2 (different computers).
Thanks,
joostje
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joostje@komputilo.org
Subject: Re: create index right after create table not working?
In-Reply-To: <20030315235332.GA32358@co.uea.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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