Re: [ADMIN] How to uses self query plan
On Dec 19, 2007 1:57 AM, olivier boissard <[EMAIL PROTECTED]> wrote: > Scott Marlowe a écrit : > > > On Dec 18, 2007 4:21 PM, [EMAIL PROTECTED] > > <[EMAIL PROTECTED]> wrote: > > > >> Hello, > >> > >> My purpose is to uses different query plan depending on queries > >> I got the following problem : some queries are taking too much time and > >> postgresql does not take the most relevant index in account. > >> I search on internet and found that no HINT function was available in > >> postgresl. > >> I tried to change indexes parameters in postgresql.conf but eache time I > >> set a param to OFF (example nested_loop ,seqscan, ...) , some queries > >> freeze database . > >> > >> How can we force postgresql to use a plan ? > >> > > > > That's not how we do things in postgresql land (usually). > > > > You should figure out WHY your queries are picking the wrong plan, and > > then see if you can get them to pick the right ones. If it's a query > > planner bug, you report it here, or the perform or general lists, and > > it gets fixed. Generic hints aren't likely to happen any time soon, > > although I do believe 8.3 is introducing function costing of some > > kind, which seems like a useful idea. > > > > But, back to fixing your slow queries. > > > > 1: Increase statistics targets on the guilty columns and reanalyze. > > 2: Run explain analyze select and post the output here. > > 3: ??? > > 4: profit? > > > Thanks for reply > > I will extract some queries. > I want to precise that I use postgresql 8.1.0 . First step would be to update to the latest 8.1.x version, whatever that is. There have been a LOT of fixes in the 8.1 branch since 8.1.0 came out, and one might be a fix for your query problem. minor updates are strictly bug and security fixes, so they're not likely to cause any problems. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Scripting postgres commands
I can run the following command from a command prompt with no problem on my database. /usr/bin/vacuumdb --verbose --analyze --all -U postgres But if I try to execute it via a bash shell script, I receive the following error. The role does exist in the database. does not existnot connect to database postgres: FATAL: role "postgres I am new to postgresql so any help would be greatly appreciated. Thanks Craig Craig Ferry Sr. Database Administrator WESCO Distribution, Inc. Suite 700 225 West Station Square Drive Pittsburgh, PA 15219 P:412-454-2835 F:412-222-7535 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Scripting postgres commands
"Ferry, Craig" <[EMAIL PROTECTED]> writes: > I can run the following command from a command prompt with no problem on my > database. > /usr/bin/vacuumdb --verbose --analyze --all -U postgres > But if I try to execute it via a bash shell script, I receive the following > error. The role does exist in the database. > does not existnot connect to database postgres: FATAL: role "postgres > I am new to postgresql so any help would be greatly appreciated. Given the curious formatting of the complaint, it looks like what the database is actually seeing as the requested username is "postgres\r" (ie, there's a carriage return or perhaps a newline character tacked onto the name). Better check your quoting in the shell script. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] How to uses self query plan
You're right . I will begin by migrate postgresql version. postgresql is now to 8.3 version . A lot of performance inprovements have been made in this last version . In any case I must do a pg_dump/pg_restore. So , Should I try directly 8.3 version or is it more cautious to stay in 8.1.x version ? Olivier Scott Marlowe a écrit : On Dec 19, 2007 1:57 AM, olivier boissard <[EMAIL PROTECTED]> wrote: Scott Marlowe a écrit : On Dec 18, 2007 4:21 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hello, My purpose is to uses different query plan depending on queries I got the following problem : some queries are taking too much time and postgresql does not take the most relevant index in account. I search on internet and found that no HINT function was available in postgresl. I tried to change indexes parameters in postgresql.conf but eache time I set a param to OFF (example nested_loop ,seqscan, ...) , some queries freeze database . How can we force postgresql to use a plan ? That's not how we do things in postgresql land (usually). You should figure out WHY your queries are picking the wrong plan, and then see if you can get them to pick the right ones. If it's a query planner bug, you report it here, or the perform or general lists, and it gets fixed. Generic hints aren't likely to happen any time soon, although I do believe 8.3 is introducing function costing of some kind, which seems like a useful idea. But, back to fixing your slow queries. 1: Increase statistics targets on the guilty columns and reanalyze. 2: Run explain analyze select and post the output here. 3: ??? 4: profit? Thanks for reply I will extract some queries. I want to precise that I use postgresql 8.1.0 . First step would be to update to the latest 8.1.x version, whatever that is. There have been a LOT of fixes in the 8.1 branch since 8.1.0 came out, and one might be a fix for your query problem. minor updates are strictly bug and security fixes, so they're not likely to cause any problems. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] problem with compilation of dynalloc2 segmentation fault
Did anybody face the same problem during the compilation of postgresql 8.2.5 on suse 10.2? I'm using gmake 3.81 gcc -I../../include -I../../../../../src/interfaces/ecpg/include -I../../../../../src/interfaces/libpq -I../../../../../src/include -D_GNU_SOURCE -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing dynalloc2.c -L../../ecpglib -L../../pgtypeslib -L../../../../../src/port -Wl,-rpath,'/usr/local/pgsql/lib' -lecpg -lpgtypes -L../../../../../src/interfaces/libpq -lpq -lpgport -lz -lreadline -lcrypt -ldl -lm -o dynalloc2 dynalloc2.pgc: In function âmainâ: dynalloc2.pgc:55: internal compiler error: Segmentation fault Please submit a full bug report, with preprocessed source if appropriate. See http://bugs.opensuse.org>; for instructions. gmake[5]: *** [dynalloc2] Error 1 gmake[5]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg/test/sql' gmake[4]: *** [all] Error 2 gmake[4]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg/test' gmake[3]: *** [all] Error 2 gmake[3]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src' gmake: *** [all] Error 2 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] How to uses self query plan
[EMAIL PROTECTED] wrote: > You're right . > I will begin by migrate postgresql version. > postgresql is now to 8.3 version . A lot of performance inprovements > have been made in this last version . > In any case I must do a pg_dump/pg_restore. > So , Should I try directly 8.3 version or is it more cautious to stay in > 8.1.x version ? > It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if you're also on development stage, you can use 8.3. Otherwise, it's better to use the latest stable available, which means 8.2.5. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] How to uses self query plan
On Dec 19, 2007 4:23 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > You're right . > I will begin by migrate postgresql version. > postgresql is now to 8.3 version . A lot of performance inprovements > have been made in this last version . > In any case I must do a pg_dump/pg_restore. > So , Should I try directly 8.3 version or is it more cautious to stay in > 8.1.x version ? No, not what I meant! I meant to go to the latest 8.1 version. i.e. 8.1.0 has lots of bugs that 8.1.10 (or whatever the latest 8.1 is) has fixed. If you wanna migrate to 8.2.5 that would probably be ok, but 8.3 isn't quite done yet. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] How to uses self query plan
Guillaume Lelarge a écrit : [EMAIL PROTECTED] wrote: You're right . I will begin by migrate postgresql version. postgresql is now to 8.3 version . A lot of performance inprovements have been made in this last version . In any case I must do a pg_dump/pg_restore. So , Should I try directly 8.3 version or is it more cautious to stay in 8.1.x version ? It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if you're also on development stage, you can use 8.3. Otherwise, it's better to use the latest stable available, which means 8.2.5. I want in fact to make test on a server before , when validated migrate on production server , so 8.2.5 is more appropriate. Will I be able to keep my postgresql.conf file or is it not compatible ? Olivier . ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] How to uses self query plan
On Dec 19, 2007 5:17 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Guillaume Lelarge a écrit : > > [EMAIL PROTECTED] wrote: > > > >> You're right . > >> I will begin by migrate postgresql version. > >> postgresql is now to 8.3 version . A lot of performance inprovements > >> have been made in this last version . > >> In any case I must do a pg_dump/pg_restore. > >> So , Should I try directly 8.3 version or is it more cautious to stay in > >> 8.1.x version ? > >> > >> > > > > It mostly depends on you. PostgreSQL 8.3 is still in beta phase. So, if > > you're also on development stage, you can use 8.3. Otherwise, it's > > better to use the latest stable available, which means 8.2.5. > > > > > > > I want in fact to make test on a server before , when validated migrate > on production server , so 8.2.5 is more appropriate. > Will I be able to keep my postgresql.conf file or is it not compatible ? No, you'll need to edit the new one by hand to reflect the changes you've made in the old one. Some things get renamed from one to the next, and often newer versions of pgsql have new settings you that the old version's postgresql.conf wouldn't know about. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Dump database more than 1 flat file
Sorry to insist in this question, but now I was doing some test and I notice that is a good idea to use split utility. This is because the output file got X bytes specified, cut the rest of the line and will not import after. In my example, I generated more than 2 files equal to 1MB. But already first file cut the last line. Even join with the second file, the last line from first file is broken and it's not possible to import this way. How can I transport more than 35G (just one table) in one DVD? Could you help? Thank you Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Dump database more than 1 flat file
Try splitting by line count instead of size: -l, --lines=NUMBER put NUMBER lines per output file pg_dump | split -l 32000 You'll have to experiment to find how many lines will give you a roughly appropriate size for your purposes. Alternatively, another option is to get an USB external HDD (I picked up a Seagate 120gb 2.5" one recently for AU$135) and dump your data to that, then take that to the new server. Any recent 2.6 kernel shouldn't have any issues mounting it (especially formatted as ext2/3 or jfs/xfs etc) Cheers, ~p THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] including variables in copy command
Hi ALL, I am trying to use Copy command to copy data from table to a flat file. Here is an example: copy ( select * from employee where hire_date between begin_date and end_date) to '/var/data/text.txt' delimiter ';'; But my problem is the query is not able to read variable begin_date and end_date. It works fine when I passed the real value. Has anyone used variable within a copy ? Please let me know. Thanks, Dinesh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] including variables in copy command
Could you wrap it in a transaction block? begin; select * into temp_table from employee where hire_date between begin_date and end_date; copy temp_table to '/var/data/text.txt' delimiter ';'; commit; THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] strftime pattern list ?
Anyone know where I can find a full list of the strftime patterns that can be used for log_filename ? Thanks in advance.. /Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] problem with compilation of dynalloc2 segmentation fault
=?UTF-8?Q?Marcin?= <[EMAIL PROTECTED]> writes: > Did anybody face the same problem during the compilation of postgresql 8.2.5 > on suse 10.2? > ... > dynalloc2.pgc: In function âmainâ: > dynalloc2.pgc:55: internal compiler error: Segmentation fault > Please submit a full bug report, > with preprocessed source if appropriate. > See http://bugs.opensuse.org>; for instructions. This is not Postgres' problem. Either you have a broken gcc, or you have flaky hardware that causes the compiler to crash. Is the behavior repeatable if you try the build several times? If yes, file a bug report against gcc (might as well do it through the mentioned opensuse page, since given the lack of other reports, odds are good it's a SUSE-induced breakage). If not repeatable, you have bad hardware; time to try the usual clean-everything-and-reseat-connectors rain dance. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] strftime pattern list ?
Kevin Kempter <[EMAIL PROTECTED]> writes: > Anyone know where I can find a full list of the strftime patterns that can be > used for log_filename ? man strftime or if you're feeling command-line-challenged, this should provide info usable on most platforms: http://www.opengroup.org/onlinepubs/007908799/xsh/strftime.html regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] problem with compilation of dynalloc2 segmentation fault
Try to set enviroment variable LANG to C value before compilation. If you use sh shell you must execute: export LANG=C. The gcc manual documents this. It's not a compiler bug. Best regards. 2007/12/19, Marcin <[EMAIL PROTECTED]>: > > Did anybody face the same problem during the compilation of postgresql > 8.2.5 > on suse 10.2? > I'm using gmake 3.81 > > > gcc -I../../include -I../../../../../src/interfaces/ecpg/include > -I../../../../../src/interfaces/libpq -I../../../../../src/include > -D_GNU_SOURCE -O2 > -Wall -Wmissing-prototypes -Wpointer-arith -Winline > -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing > dynalloc2.c -L../../ecpglib > -L../../pgtypeslib > -L../../../../../src/port -Wl,-rpath,'/usr/local/pgsql/lib' > -lecpg -lpgtypes -L../../../../../src/interfaces/libpq -lpq -lpgport -lz > -lreadline -lcrypt -ldl -lm -o dynalloc2 > dynalloc2.pgc: In function âmainâ: > dynalloc2.pgc:55: internal compiler error: Segmentation fault > Please submit a full bug report, > with preprocessed source if appropriate. > See http://bugs.opensuse.org>; for instructions. > gmake[5]: *** [dynalloc2] Error 1 > gmake[5]: Leaving directory > `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg/test/sql' > gmake[4]: *** [all] Error 2 > gmake[4]: Leaving directory > `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg/test' > gmake[3]: *** [all] Error 2 > gmake[3]: Leaving directory > `/home/.../tmp/postgresql-8.2.5/src/interfaces/ecpg' > gmake[2]: *** [all] Error 2 > gmake[2]: Leaving directory `/home/.../tmp/postgresql-8.2.5 > /src/interfaces' > gmake[1]: *** [all] Error 2 > gmake[1]: Leaving directory `/home/.../tmp/postgresql-8.2.5/src' > gmake: *** [all] Error 2 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings >
