Thread: removing a DB??
hello, i have a nice command to create a db 'createdb', but hows when you want to get rid of a DB?? searched for a removedb script.... Took me some time to read through the man page, find out how the command actually works, look up the SQL commands and find out that there's also a DROP DATABASE SQL-command... And then looking up that there'S really a dropdb command.... would be really nice if in the man page there was a short ref, telling me about related commands..... Then the question arouse, if i can create and destroy databases from SQL, building my install script, it would be nice to test for the presence of a DB and in case of inexistence, create it on the fly. For this it would be nice if i could have found for each possible command in psql the SQL counterpart, notably, how to list all available DB's through SQL. BTW shouldn't \l display also the internal DB's ? If it would i could have searched for the right table.... -- ciao bboett ============================================================== bboett@earthling.net http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett =============================================================== the total amount of intelligence on earth is constant. human population is growing....
Hello Bruno, you get a list of all available databases by issuing a query on "pg_database": select * from pg_database; What do you mean with "internal DB's"? Regards, Jens Bruno Boettcher schrieb: > > hello, > > i have a nice command to create a db 'createdb', but hows when you want > to get rid of a DB?? searched for a removedb script.... > Took me some time to read through the > man page, find out how the command actually works, look up the SQL > commands and find out that there's also a DROP DATABASE SQL-command... > > And then looking up that there'S really a dropdb command.... > > would be really nice if in the man page there was a short ref, telling > me about related commands..... > > Then the question arouse, if i can create and destroy databases from > SQL, building my install script, it would be nice to test for the > presence of a DB and in case of inexistence, create it on the fly. > > For this it would be nice if i could have found for each possible command > in psql the SQL counterpart, notably, how to list all available DB's > through SQL. > > BTW shouldn't \l display also the internal DB's ? If it would i could > have searched for the right table.... > > -- > ciao bboett > ============================================================== > bboett@earthling.net > http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett > =============================================================== > the total amount of intelligence on earth is constant. > human population is growing.... -- Herzliche Grüße, Jens Hartwig ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================
> I have two tables: t_haus is about 1400 row and t_host has 364000 entries. Both tables are indexed on edvnr. I did a vacuum on my db and all indices are rebuild. I want to delete all Entries in t_haus where a row can be found in t_host. When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the database performs extremely bad. explain delete from t_haus where t_haus.edvnr=t_host.edvnr; NOTICE: QUERY PLAN: Merge Join (cost=52178.53..56754.63 rows=6299767 width=14) -> Sort (cost=52038.25..52038.25 rows=364359 width=4) -> Seq Scan on t_host (cost=0.00..11700.59 rows=364359 width=4) -> Sort (cost=140.27..140.27 rows=1729 width=10) -> Seq Scan on t_haus (cost=0.00..47.29 rows=1729 width=10) EXPLAIN What can I do to speed it up? I've also had troubles with DELETE before (when doing joins in the WHERE clause). Hans
Hans-Jürgen Schönig <hs@cybertec.at> writes:
> I have two tables: t_haus is about 1400 row and t_host has 364000 entries.
> Both tables are indexed on edvnr. I did a vacuum on my db and all indices
> are rebuild.
> I want to delete all Entries in t_haus where a row can be found in t_host.
> When using "delete from t_haus where t_haus.edvnr=t_host.edvnr; " the
> database performs extremely bad.
> explain delete from t_haus where t_haus.edvnr=t_host.edvnr;
> NOTICE: QUERY PLAN:
> Merge Join (cost=52178.53..56754.63 rows=6299767 width=14)
> -> Sort (cost=52038.25..52038.25 rows=364359 width=4)
> -> Seq Scan on t_host (cost=0.00..11700.59 rows=364359 width=4)
> -> Sort (cost=140.27..140.27 rows=1729 width=10)
> -> Seq Scan on t_haus (cost=0.00..47.29 rows=1729 width=10)
I wonder if a hash join would be faster. What does EXPLAIN show if
you first do "set enable_mergejoin to off"? What's the actual
performance in both cases?
Also, it's possible that the performance problem isn't the fault of the
plan at all. Are there multiple rows in t_host matching the deletable
rows of t_haus? I'm wondering if there's some speed penalty associated
with trying to delete the same row multiple times in one command...
regards, tom lane
On Mon, Nov 27, 2000 at 12:34:38PM +0100, Jens Hartwig allegedly wrote: > Hello Bruno, > > you get a list of all available databases by issuing a query on > "pg_database": > > select * from pg_database; > > What do you mean with "internal DB's"? > > Regards, Jens You can also get a list of databases by executing psql -l from the commandline. My 0.02 euro, Mathijs -- "It is a great thing to start life with a small number of really good bookswhich are your very own". Sir Arthur ConanDoyle (1859-1930)