[SQL] backup of a specific schema
hi, is there anyway to backup/restore a specfic schema in a database? -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] backup of a specific schema
check pg_dump options, is there --- Kenneth Gonsalves <__> wrote: > hi, > is there anyway to backup/restore a specfic schema in a database? > -- > regards > kg > > http://www.onlineindianhotels.net - fastest hotel search website in > the world > http://www.ootygolfclub.org > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Aggregate query for multiple records
shouldn't take that long, I would think. You have indexes on wid and date? Troy > > Hello, I am new to the list, my apology if this question is beyond the > scope or charter of this list. > > My questions is: > What is the best method to perform an aggregate query to calculate > sum() values for each distinct wid as in the example below, but except > for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). > > Also, performance wise, would it be better to build a function for this > query. The table has 9 million records and these aggregate queries > take hours. > > > SELECT >SUM(oil) as sumoil, SUM(hours) as sumhours, > FROM >(SELECT oil, hours prd_data WHERE wid='01/1-6-1-30w1/0' > ORDER BY date LIMIT 6) subtable > ; > > > Table description: >Table "prd_data" > Column | Type | Modifiers > +---+--- > date | integer | > hours | real | > oil| real | > gas| real | > water | real | > pwid | integer | > wid| character varying(20) | > year | smallint | > Indexes: wid_index6 > > > Actual table (prd_data), 9 million records: > >date | hours | oil | gas | water | pwid | wid | year > +---+---+--+---+--+-+-- > 196507 | 360 | 159.4 | 11.3 | 40.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196508 | 744 | 280 | 20 | 27.2 | 413 | 01/1-1-1-31w1/0 | 1965 > 196509 | 360 | 171.1 | 11.4 | 50.4 | 413 | 01/1-1-1-31w1/0 | 1965 > 196510 | 744 | 202.1 | 25 | 89.8 | 413 | 01/1-1-1-31w1/0 | 1965 > 196512 | 744 | 201.3 | 23.8 | 71.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196511 | 720 | 184 | 17.6 | 78.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196610 | 744 | 99.8 | 15.4 | 53.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 196612 | 744 |86 | 12.8 | 36.1 | 413 | 01/1-1-1-31w1/0 | 1966 > 196611 | 720 |86 | 12.6 | 41.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 196601 | 744 | 191.6 | 22.6 | 50.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 200301 | 461 | 68.8 | 0 | 186.3 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200310 | 740 | 446.3 | 0 | 563.1 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200306 | 667 | 92.1 | 0 | 968.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200304 | 0 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200308 | 457 | 100.7 | 0 | 82.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200307 | 574 |78 | 0 | 752 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200312 | 582 | 360.9 | 0 | 569 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200311 | 681 | 260.8 | 0 | 563.9 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200305 | 452 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200309 | 637 | 244.6 | 0 | 193.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > (20 rows) > > > > Thanks, > > -- > Scott A. Gerhardt, P.Geo. > Gerhardt Information Technologies > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] backup of a specific schema
On Friday 27 August 2004 01:17 pm, Michalis Kabrianis wrote: > Kenneth Gonsalves wrote: > > hi, > > is there anyway to backup/restore a specfic schema in a database? > > Hi, > isn't this what pg_dump --schema=SCHEMA does? thanx - was looking in the wrong place in the manual -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] from PG_DUMP to CVS
At 02:38 PM 27/08/2004, Josh Berkus wrote: If it's Perl, I'd be interested in contributing. I've long needed something like this myself. My thinking at this stage is to try to get pg_dump/restore to produce the output directly. Something like: some-dbname/create.sql some-dbname/drop.sql some-dbname/econding.sql ... some-dbname/some-schema/TABLE/sometable/create.sql some-dbname/some-schema/TABLE/sometable/drop.sql ... some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql ... some-dbname/some-schema/ACL/some-table.sql some-dbname/some-schema/ACL/some-function(int).sql ... etc. This would be easy. Question is, how useful would it be? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] GRANT ON all tables
Hi there, I need to know how can I give privileges to all the tables in a database. For example, I have one database named "test" .This database contains about 30 tables. I want to grant SELECT and DELETE privileges to the 'test_user' by typing GRANT just once (something like GRANT SELECT, DELETE ON all_tables TO test_user). It is possible? I search PostgreSQL docs and I can't find out... Thanks Nosy _ Message sent using ITCNet free webmailer (http://www.easymail.ro) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] from PG_DUMP to CVS
That depends on the use you plan to... as a backup, useless, as a documentary tool, very (at least, for what I need). Our way of developing things is to set up a development box, and set up the tables, functions, etc etc etc using a set of tools... the only problem is that these do not provide any kind of versioning control. At least, not one that the user will be able to understand or use. The problem that we face right now is that we provide not only versioning control, but web access to it! CVS is agreed on (both parties like it), but we need a quick way to convert the gazillions of tables, rules, views, functions, procedures, etc etc etc to an output format that can be moved to the CVS and, if necessity arouse, use the single files to perform maintenance on the user's end. This way, we are happy because we don't need to revise our way of development and the user is happy because he sees what we're doing, and he's able trace the changes. One other good things is that if this is done the right way, almost no human time is required to provide the info, and the tool can run daily. regards, Riccardo --- Philip Warner <__> wrote: > At 02:38 PM 27/08/2004, Josh Berkus wrote: > >If it's Perl, I'd be interested in contributing. I've long needed > something > >like this myself. > > My thinking at this stage is to try to get pg_dump/restore to produce > the > output directly. Something like: > > some-dbname/create.sql > some-dbname/drop.sql > some-dbname/econding.sql > ... > some-dbname/some-schema/TABLE/sometable/create.sql > some-dbname/some-schema/TABLE/sometable/drop.sql > ... > some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql > some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql > ... > some-dbname/some-schema/ACL/some-table.sql > some-dbname/some-schema/ACL/some-function(int).sql > ... > > etc. > > This would be easy. Question is, how useful would it be? > > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au |/ \| > |---- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Aggregate query for multiple records
Scott, > > SELECT wid, > > (SELECT SUM(oil) FROM prd_data pd2 > > WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_oil, > > (SELECT SUM(hours) FROM prd_data pd2 > > WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_hours > > FROM prd_data > > ORDER BY wid; > > There is only one table being queried (prd_data). What is the purpose > of "pd2"? It's a table alias. There may be only one table, but to do this in a single query you need to reference 3 "instances" of that same table. Hmmm ... let me make the distinction more clear: SELECT wid, (SELECT SUM(oil) FROM prd_data prd2 WHERE prd2.wid = prd1.wid ORDER BY "date" LIMIT 6) as tot_oil, (SELECT SUM(hours) FROM prd3 WHERE prd3.wid = prd1.wid ORDER BY "date" LIMIT 6) as tot_hours FROM prd_data prd1 ORDER BY wid; -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] from PG_DUMP to CVS
Philip,
> My thinking at this stage is to try to get pg_dump/restore to produce the
> output directly. Something like:
Hey, you do what you want, of course. However, it seems to me that hacking
AutoDoc would be a *lot* less effort than hacking pg_dump.
Interestingly, though, I was talking to someone on IRC (Neil? Gavin?) some 6
months ago or so about hacking a "PSQL-FS" that is, an interface to the
*live* database which would look like a filesystem.Were this done, it
would be child's play to rsync it with an archive. Whomever it was didn't
seem to think it too challenging a task.
> some-dbname/create.sql
> some-dbname/drop.sql
> some-dbname/econding.sql
> ...
> some-dbname/some-schema/TABLE/sometable/create.sql
> some-dbname/some-schema/TABLE/sometable/drop.sql
Hmmm. I see a slightly different structure below the schema level:
some-dbname/some-schema/TABLES/sometable/create.sql
some-dbname/some-schema/TABLES/sometable/indexes.sql
some-dbname/some-schema/TABLES/sometable/constraints.sql
some-dbname/some-schema/TABLES/sometable/triggers.sql
some-dbname/some-schema/TABLES/sometable/rules.sql
some-dbname/some-schema/VIEWS/someview/create.sql
some-dbname/some-schema/VIEWS/someview/rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction/param{codes}.sql
some-dbname/some-schema/TYPES/sometype/create.sql
some-dbname/some-schema/OPERATORS/someoperator/create.sql
However, the above is somewhat unfriendly to CVS, as one can't drop
directories in CVS and that would be entailed in the dropping of any objects.
An alternative would be:
some-dbname/some-schema/TABLES/sometable-create.sql
some-dbname/some-schema/TABLES/sometable-indexes.sql
some-dbname/some-schema/TABLES/sometable-constraints.sql
some-dbname/some-schema/TABLES/sometable-triggers.sql
some-dbname/some-schema/TABLES/sometable-rules.sql
some-dbname/some-schema/VIEWS/someview-create.sql
some-dbname/some-schema/VIEWS/someview-rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
some-dbname/some-schema/TYPES/sometype-create.sql
some-dbname/some-schema/OPERATORS/someoperator-create.sql
or even:
some-dbname/some-schema/TABLES/sometable.sql
some-dbname/some-schema/VIEWS/someview.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
some-dbname/some-schema/TYPES/sometype.sql
some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
In this last, all dependant objects of, for example, a table (rules, triggers,
indexes, etc. ) would be rolled up into one file. It's this last version
that I personally favor.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Aggregate query for multiple records
Scott, > >> I tried your query but it doesn't seem to work. The interpreter > >> expects prd2.date to be grouped but this doesn't make sence, see > >> below: > > > > Oh, yeah, darn it. > > > > Hmmm ... maybe you could explain the purpose of selecting just 6? > > This query > > is going to get a *lot* uglier if there isn't another way to > > accomplish it. > > The purpose of selecting the first 6 is we need sum values for just the > first 3, 6 and 12 months oil/water production for every oil well in the > database to perform statistical calculations. Optionally, we also need > the _last_ 6 months of oil and water production. > The prd_data table has total oil for each month but some wells have 500 > months of data, we just want the first 6. Well, here's my suggestion, to make this query both easier and faster: 1) Add a column called "month_prod", integer, to the table. This is the "number of months in production". 2) Populate this query with a calculated difference on your "date" column against the first appearance of that WID (I'm assuming that each increment of "date" = 1 month) UPDATE prd_data SET months_prod = prd_data."date" - prd2."date" + 1 FROM prd_data prd2 WHERE prd_data.wid = prd2.wid AND prd2."date" = ( SELECT "date" FROM prd_data prd3 WHERE prd3.wid = prd2.wid ORDER BY "date" LIMIT 1 ); (warning: the above will take a long time and floor your server. Make sure to do VACUUM FULL ANALYZE prd_data afterwards.) (if you can't get the above to complete, which is possible depending on your hardware, you could do it procedurally in a programmming language) 3) Then running your aggregate becomes very easy/fast: SELECT wid, sum(oil) as tot_oil, sum(hours) as tot_hours FROM prd_data WHERE months_prod < 7 GROUP BY wid ORDER BY wid; -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] GRANT ON all tables
On Fri, Aug 27, 2004 at 15:30:32 -, Nosyman <[EMAIL PROTECTED]> wrote: > Hi there, > > I need to know how can I give privileges to all the tables in a database. > For example, I have one database named "test" .This database contains about > 30 tables. I want to grant SELECT and DELETE privileges to the 'test_user' > by typing GRANT just once (something like GRANT SELECT, DELETE ON > all_tables TO test_user). It is possible? I search PostgreSQL docs and I > can't find out... There is no command to do this. You can write a script that queries the information schema and then issues a grant command for each table. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] help with trigger
Maybe I've missed it but can someone plese help me with this? Brgds and thanks in advance, --- Dino Vliet <[EMAIL PROTECTED]> wrote: > Hi folks, > I'm new to PostgreSQL and am busy tring to work with > it. Of pl/pgsql I know even less and that's the part > I > have a question on right now. I have this nice > example > to get me started with > pl/pgsql... > > I have a table with the schedule of a service my > sport > team wants to offer: > > Table Schedule > name length startdate enddateday > soccer 4 01-sep-2004 30-sep-2004 > Mon > tennis 4 01-sep-2004 30-sep-2004 > Wed > > This means, that I want to offer soccer weekly, for > a > total of 4 weeks as from 1st sep till 30 sep on > Mondays! > > Now, what I do want is a second table that creates > all > the dates on which there will be soccer training if > I > finish entering this record in table Schedule. So, > then the other table, called ScheduledDates has the > follwing records: > > Table ScheduleDates > nameoccurrence date > soccer 1 06-sep-2004 > soccer 2 13-sep-2004 > soccer 3 20-sep-2004 > soccer 4 27-sep-2004 > > Can someone help me with writing this as a trigger > in > pl/pgsql? > > Another variant is that the trigger starts when the > table Schedule is populated, but it not just > automatically creates the 4 records as described > above, but because the date 20-sep-2004 is a > national > holiday and is in table Exceptions, the sheme just > shifts one up and the occurrence 4 becomes > occurrence > 3 and the last schedule date becomes monday the 4th > of > october! > > I hope you can help me with this one, will put me > way > up the learning curve of pl/pgsql > > > > > __ > Do you Yahoo!? > New and Improved Yahoo! Mail - 100MB free storage! > http://promotions.yahoo.com/new_mail > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Copy command freezes but INSERT works fine with trigger on insert.
I tried to add up records 2 different ways: 1. using command [copy messageinfo (user_id, receivedtime, filename, sendersstring, subject, hasattachments, groupmsg, msgsize, version ) FROM '/meridtemp/messageinfo.dat';] from psql 2. using simple INSERT INTO messageinfo ( user_id ) VALUES( 1728::int8 ); In 2nd case it worked but not in 1st one. Why? Later I did an experiment & repeated it few times. After copy command is running for a while from within psql and trying to insert 60,000 records I interrupted it by pressing CTRL+C few times. Each time I had different line within addmsgcountSync where it stopped. It tells me that copy command does not freeze on one particular statement but it did not insert a single record. For this table messageinfo I have trigger: CREATE TRIGGER msgInjector AFTER INSERT ON messageinfo FOR EACH ROW EXECUTE PROCEDURE addmsgcountSync(); CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS ' DECLARE currentTime injector.lastreceivedtime%TYPE; vlastreceivedtime injector.lastreceivedtime%TYPE; userIdRec RECORD; vID messageinfo.user_id%TYPE; injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE injector.id = tt::int8 ; BEGIN vID = NEW.user_id; IF ( vID IS NOT NULL ) THEN -- Find out lastrecievedtime we need cursor OPEN injectorCursor( vID ); FETCH injectorCursor INTO userIdRec; vlastreceivedtime = userIdRec.lastreceivedtime; CLOSE injectorCursor; currentTime = CURRENT_TIMESTAMP; IF vlastreceivedtime < currentTime THEN vlastreceivedtime = currentTime; END IF; -- To make sure time of last message is not newer than lastreceivedtime time IF vlastreceivedtime < NEW.receivedtime THEN vlastreceivedtime = NEW.receivedtime; END IF; -- Stopes copy command but not insert one ? UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = vlastreceivedtime WHERE injector.id = vID::int8; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql'; = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Copy command freezes but INSERT works fine with trigger oninsert.
The copy command will run as a single transaction -- all succeed or all fail, I think, so if you interrupt it no rows will be loaded. The inserts -- unless wrapped into a transaction with a "begin; ... commit;" block will each be a single transaction. Check you postgres log file for the time when they copy was runnint --- you should see it writing something like this as it does its logging: 2004-08-18 09:56:26 LOG: removing transaction log file "00220089" 2004-08-18 09:56:26 LOG: removing transaction log file "00220087" ... In and of itself I don't see why the trigger would stop copy (although performance might be an issue), but I am rather unacquainted with triggers in postgres, so perhaps someone more knowledgable could comment. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Artimenko Igor [mailto:[EMAIL PROTECTED] Sent: Fri 8/27/2004 1:16 PM To: [EMAIL PROTECTED] Cc: Subject:[SQL] Copy command freezes but INSERT works fine with trigger oninsert. I tried to add up records 2 different ways: 1. using command [copy messageinfo (user_id, receivedtime, filename, sendersstring, subject, hasattachments, groupmsg, msgsize, version ) FROM '/meridtemp/messageinfo.dat';] from psql 2. using simple INSERT INTO messageinfo ( user_id ) VALUES( 1728::int8 ); In 2nd case it worked but not in 1st one. Why? Later I did an experiment & repeated it few times. After copy command is running for a while from within psql and trying to insert 60,000 records I interrupted it by pressing CTRL+C few times. Each time I had different line within addmsgcountSync where it stopped. It tells me that “copy” command does not freeze on one particular statement but it did not insert a single record. For this table messageinfo I have trigger: CREATE TRIGGER msgInjector AFTER INSERT ON messageinfo FOR EACH ROW EXECUTE PROCEDURE addmsgcountSync(); CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS ' DECLARE currentTime injector.lastreceivedtime%TYPE; vlastreceivedtime injector.lastreceivedtime%TYPE; userIdRec RECORD; vID messageinfo.user_id%TYPE; injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE injector.id = tt::int8 ; BEGIN vID = NEW.user_id; IF ( vID IS NOT NULL ) THEN -- Find out lastrecievedtime we need cursor OPEN injectorCursor( vID ); FETCH injectorCursor INTO userIdRec; vlastreceivedtime = userIdRec.lastreceivedtime; CLOSE injectorCursor; currentTime = CURRENT_TIMESTAMP; IF vlastreceivedtime < currentTime THEN vlastreceivedtime = currentTime; END IF; -- To make sure time of last message is not newer than lastreceivedtime time IF vlastreceivedtime < NEW.receivedtime THEN vlastreceivedtime = NEW.receivedtime; END IF; -- Stopes copy command but not insert one ? UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = vlastreceivedtime WHERE injector.id = vID::int8; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql'; = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Copy command freezes but INSERT works fine with trigger on insert.
Copy from .. works fine for 1000 records to load. For data set of 6 records I could never get it finish. And I'm planing to reload 1000,000 records. So there is a limit somewhere between 1,000 & 60,000 since it starts working slower. The only question for me left is. What config parameter(s) I should increase to speed up copy command and to which values rougthly? Also do you know config settings to see if copy operation progresses or waits for something? __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] Why those queries do not utilize indexes?
Thanks a lot but none of those queries was effecient. 1. This query is longest. explain analyze SELECT * FROM messageinfo WHERE user_id::bigint = 2 and msgstatus::smallint = 0; 2. This one is the same as my original in performance and bad index usage. explain analyze SELECT * FROM messageinfo WHERE user_id = 2::bigint and msgstatus = 0::smallint; --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > Artimenko Igor wrote: > > I could force Postgres to use the best index by removing condition "msgstatus = > > CAST( 0 AS > > smallint );" from WHERE clause & set enable_seqscan to off; > > Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( > > best index > ). > > > WHERE user_id::bigint = 2 and msgstatus:smallint = 0; > > Sincerely, > > Joshau D. Drake > > > > > > But unfortunatelly It does not resolve my problem. I can not remove above > > condition. I need to > > find a way to use whole condition "WHERE user_id = CAST( 2 AS BIGINT ) and > > msgstatus = > CAST( 0 > > AS smallint );" and still utilyze index. > > > > Yes you are right. Using "messagesStatus" index is even worse for my data set then > > sequential > > scan. > > > > Igor Artimenko > > > > --- Dennis Bjorklund <[EMAIL PROTECTED]> wrote: > > > > > >>On Fri, 27 Aug 2004, Artimenko Igor wrote: > >> > >> > >>>1. Sequential search and very high cost if set enable_seqscan to on; > >>>Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 ) > >>> > >>>2. Index scan but even bigger cost if set enable_seqscan to off; > >>>Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 ) > >> > >>So pg thinks that a sequential scan will be a little bit faster (The cost > >>is a little bit smaller). If you compare the actual runtimes maybe you > >>will see that pg was right. In this case the cost is almost the same so > >>the runtime is probably almost the same. > >> > >>When you have more data pg will start to use the index since then it will > >>be faster to use an index compared to a seq. scan. > >> > >>-- > >>/Dennis Björklund > >> > >> > > > > > > > > > > > > ___ > > Do you Yahoo!? > > Win 1 of 4,000 free domain names from Yahoo! Enter now. > > http://promotions.yahoo.com/goldrush > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com > Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL > > begin:vcard > fn:Joshua D. Drake > n:Drake;Joshua D. > org:Command Prompt, Inc. > adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA > email;internet:[EMAIL PROTECTED] > title:Consultant > tel;work:503-667-4564 > tel;fax:503-210-0334 > note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL > support > provider. We provide the only commercially viable integrated PostgreSQL replication > solution, > but also custom programming, and support. We authored the book Practical > PostgreSQL, the > procedural language plPHP, and adding trigger capability to plPerl. > x-mozilla-html:FALSE > url:http://www.commandprompt.com/ > version:2.1 > end:vcard > > = Thanks a lot Igor Artimenko I specialize in Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data Modeling __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] backup of a specific schema
Kenneth Gonsalves wrote: On Friday 27 August 2004 01:17 pm, Michalis Kabrianis wrote: Kenneth Gonsalves wrote: hi, is there anyway to backup/restore a specfic schema in a database? Hi, isn't this what pg_dump --schema=SCHEMA does? thanx - was looking in the wrong place in the manual Which one ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
