Re: [SQL] Query history file
~/.psql_history is everything you typed in psql On Sat, 02 Apr 2005 01:42:05 +0200, Mauro Bertoli <[EMAIL PROTECTED]> wrote: Hi, I've installed a Postgres 8.0. There's a history file with all executed queries? Thanks! ___ Nuovo Yahoo! Messenger: E' molto pià divertente: Audibles, Avatar, Webcam, Giochi, Rubricaâ Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How to store directory like structures?
Hello! I want to store some structure like: CREATE TABLE node ( nodeid SERIAL PRIMARY KEY, parent INT REFERENCES node(nodeid) ON UPDATE CASCADE ON DELETE CASCADE, label TEXT, UNIQUE (parent, label), ... data ... ); The label is used to map a node to a directory like strukture, so i can have a function directory_for(nodeid) which gives me /root_label/parent_label/parent_label/my_label (root labels have NULL as parent) The problem is the ammount of queries when i've got deep nodes, and I often have to query if a node is "in path" of another node. Is there a good solution to build directory-tree like datastruktures? I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems to do what i want, but I've no idea (and probalbly no chance) to get that running on my system ;-( Thanks, AXEL. ---(end of broadcast)--- TIP 3: 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: [SQL] a very big table
- Original Message - From: "_moray" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 29, 2005 12:25 PM Subject: [SQL] a very big table hullo all, I have a problem with a table containing a lot of data. referred tables "inserzionista" and "pubblicazioni" (referenced 2 times) have resp. 1909 tuples and 8300 tuples, while this one 54942. now the problem is that it is slow, also a simple "select * from pubblicita". (it takes 5-6 seconds on my [EMAIL PROTECTED],6Ghz laptop...) I tried using some indexes, but the main problem is that I am using a php script to access the data that builds the query according to user input. Generally, you need to have an index for any column that will appear in a 'where' clause or be referenced as a foreign key. The referencing columns should be declared "unique" or "primary key" and will also then be indexed. So, any column that is on the left or right of an '=' sign in a join or a 'where' clause should probably be indexed. There are exceptions, but that is the general rule. Also, after you make your indices, you need to remember to vacuum analyze. As you can see it is a quite heavy query...but also with simple queries: === cioe2=# explain SELECT * from pubblicita; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..2863.42 rows=54942 width=325) (1 row) cioe2=# explain SELECT * from pubblicita where soggetto ilike 'a%'; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..3000.78 rows=54942 width=325) Filter: (soggetto ~~* 'a%'::text) (2 rows) === suggestions on how to make things smoother? (the table is below) thnx Ciro. === create table pubblicita ( codice_pubblicita bigserial, codice_inserzionista int NOT NULL, codice_pagina varchar(2), codice_materiale varchar(2), codice_pubblicazione bigint NOT NULL, data_registrazione timestamp, ripete_da bigint, soggetto text, inserto text, prezzo numeric, ns_fattura int, ns_fattura_data date, vs_fattura int, vs_fattura_data date, colore bool, data_prenotazione date, data_arrivo date, data_consegna date, note_prenotazione text, note_consegna text, note text, annullata bool DEFAULT 'f', PRIMARY KEY (codice_pubblicita), FOREIGN KEY (codice_pubblicazione) REFERENCES pubblicazioni ON UPDATE CASCADE, FOREIGN KEY (ripete_da) REFERENCES pubblicazioni (codice_pubblicazione) ON UPDATE CASCADE, FOREIGN KEY (codice_inserzionista) REFERENCES inserzionisti ON UPDATE CASCADE, FOREIGN KEY (codice_pagina) REFERENCES pagine ON UPDATE CASCADE, FOREIGN KEY (codice_materiale) REFERENCES materiali ON UPDATE CASCADE ); === ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Date/Time Conversion
beta_jgw=# update scenario1.time_test set local_hour = extract(hour from to_timestamp(to_char(gmt_date,'-MM-DD')||' '||to_char(gmt_hour,'99')||':00:00-00','-MM-DD HH24:MI:SS') at time zone 'EST'); This sounds like a stupid answer, but shouldn't that be :00:00:00? Regards, Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to store directory like structures?
On Sun, 3 Apr 2005, Axel Straschil wrote: Hello! I want to store some structure like: CREATE TABLE node ( nodeid SERIAL PRIMARY KEY, parent INT REFERENCES node(nodeid) ON UPDATE CASCADE ON DELETE CASCADE, label TEXT, UNIQUE (parent, label), ... data ... ); The label is used to map a node to a directory like strukture, so i can have a function directory_for(nodeid) which gives me /root_label/parent_label/parent_label/my_label (root labels have NULL as parent) The problem is the ammount of queries when i've got deep nodes, and I often have to query if a node is "in path" of another node. Is there a good solution to build directory-tree like datastruktures? I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems to do what i want, but I've no idea (and probalbly no chance) to get that running on my system ;-( what's a problem with ltree ? Thanks, AXEL. ---(end of broadcast)--- TIP 3: 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 Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to store directory like structures?
Hello! >> I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems > what's a problem with ltree ? I think ltree would be exactly what I need, the Problem ist that I've got absolutly no Idea how to get that thing into a running Pg 7.4 under Gentoo and a Pg 7.3 under Fedora? Is there a possibility to use ltree without using the original sources from postgresql and keep using gentoo's portage and fedora's rpm-version of postgres? Thanks, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to store directory like structures?
On gentoo (at least on my box) it's installed by default in 8.0, I believe it was installed by default, too, on 7.4.X On Sun, 03 Apr 2005 19:26:03 +0200, Axel Straschil <[EMAIL PROTECTED]> wrote: Hello! I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems what's a problem with ltree ? I think ltree would be exactly what I need, the Problem ist that I've got absolutly no Idea how to get that thing into a running Pg 7.4 under Gentoo and a Pg 7.3 under Fedora? Is there a possibility to use ltree without using the original sources from postgresql and keep using gentoo's portage and fedora's rpm-version of postgres? Thanks, AXEL. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to store directory like structures?
Use the ltree datatype ! It's made specifically for this purpose. http://www.sai.msu.su/~megera/postgres/gist/ On Sun, 03 Apr 2005 12:13:48 +0200, Axel Straschil <[EMAIL PROTECTED]> wrote: Hello! I want to store some structure like: CREATE TABLE node ( nodeid SERIAL PRIMARY KEY, parent INT REFERENCES node(nodeid) ON UPDATE CASCADE ON DELETE CASCADE, label TEXT, UNIQUE (parent, label), ... data ... ); The label is used to map a node to a directory like strukture, so i can have a function directory_for(nodeid) which gives me /root_label/parent_label/parent_label/my_label (root labels have NULL as parent) The problem is the ammount of queries when i've got deep nodes, and I often have to query if a node is "in path" of another node. Is there a good solution to build directory-tree like datastruktures? I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems to do what i want, but I've no idea (and probalbly no chance) to get that running on my system ;-( Thanks, AXEL. ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to store directory like structures?
On Sun, 3 Apr 2005, Axel Straschil wrote: Hello! I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems what's a problem with ltree ? I think ltree would be exactly what I need, the Problem ist that I've got absolutly no Idea how to get that thing into a running Pg 7.4 under Gentoo and a Pg 7.3 under Fedora? Is there a possibility to use ltree without using the original sources from postgresql and keep using gentoo's portage and fedora's rpm-version of postgres? I have no experience with those beasts, but what's wrong just untar source, configure and compile by hand ? Thanks, AXEL. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] a very big table
I'd suggest modifying your query generator to make it smarter : FROM pubblicita LEFT OUTER JOIN materiali ON (pubblicita.codice_materiale=materiali.codice_materiale) LEFT OUTER JOIN inserzionisti ON (pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista) (snip) WHERE pubblicazioni.anno ILIKE '2003%' AND inserzionisti.sigla ILIKE 'starline%' (snip) Here you don't need to LEFT JOIN, you can use a straight simple unconstrained join because the rows generated by the LEFT JOINs which have NULL in the right columns will be rejected by the WHERE clause anyway : FROM pubblicita, materiali, inserzionisti (remainder of LEFT JOINs for table which have nothing in the WHERE) (snip) WHERE pubblicazioni.anno ILIKE '2003%' AND inserzionisti.sigla ILIKE 'starline%' AND pubblicita.codice_materiale=materiali.codice_materiale AND pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista (snip) Doing this, you leave more options for the planner to choose good plans, and also to generate less of the joins (ie for instance starting on publicazioni, taking only the rows with the date condition, and then joining them to the other tables). Now, other comments : ILIKE cant' ever use an index. If you must use LIKE, use lower(column) LIKE 'something%' and create a functional index on lower(column). WHY IS THE DATE STORED AS TEXT ?? You could use a DATE field and query "pubblicazioni.anno BETWEEN '2003-01-01' AND '2003-12-31'" or any other date range. Always use the appropriate datatype. BETWEEN uses indexes. On Tue, 29 Mar 2005 18:25:55 +0200, _moray <[EMAIL PROTECTED]> wrote: hullo all, I have a problem with a table containing a lot of data. referred tables "inserzionista" and "pubblicazioni" (referenced 2 times) have resp. 1909 tuples and 8300 tuples, while this one 54942. now the problem is that it is slow, also a simple "select * from pubblicita". (it takes 5-6 seconds on my [EMAIL PROTECTED],6Ghz laptop...) I tried using some indexes, but the main problem is that I am using a php script to access the data that builds the query according to user input. f.i. I made a simple interface where a user can specify multiple filters on almost all the columns of the table and a resulting query could be: === SELECT ripete.numero as ripete_numero, pubblicita.soggetto,pubblicita.colore, pubblicazioni.anno,pubblicazioni.numero, pubblicita.codice_pubblicita,pubblicita.annullata, pubblicita.codice_pagina,pubblicita.codice_materiale, pubblicita.note,pubblicita.prezzo, testate.testata AS testata, inserzionisti.sigla AS inserzionista, materiali.descrizione AS materiale, pagine.descrizione AS pagina FROM pubblicita LEFT OUTER JOIN materiali ON (pubblicita.codice_materiale=materiali.codice_materiale) LEFT OUTER JOIN pagine ON (pubblicita.codice_pagina=pagine.codice_pagina) LEFT OUTER JOIN inserzionisti ON (pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista) LEFT OUTER JOIN pubblicazioni ON (pubblicita.codice_pubblicazione=pubblicazioni.codice_pubblicazione) LEFT OUTER JOIN testate ON (pubblicazioni.codice_testata=testate.codice_testata) LEFT OUTER JOIN pubblicazioni ripete ON (pubblicita.ripete_da=ripete.codice_pubblicazione) WHERE pubblicazioni.anno ILIKE '2003%' AND inserzionisti.sigla ILIKE 'starline%' ORDER BY testate.testata ASC LIMIT 15 OFFSET 0 === As you can see it is a quite heavy query...but also with simple queries: === cioe2=# explain SELECT * from pubblicita; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..2863.42 rows=54942 width=325) (1 row) cioe2=# explain SELECT * from pubblicita where soggetto ilike 'a%'; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..3000.78 rows=54942 width=325) Filter: (soggetto ~~* 'a%'::text) (2 rows) === suggestions on how to make things smoother? (the table is below) thnx Ciro. === create table pubblicita ( codice_pubblicita bigserial, codice_inserzionistaint NOT NULL, codice_pagina varchar(2), codice_materialevarchar(2), codice_pubblicazionebigint NOT NULL, data_registrazione timestamp, ripete_da bigint, soggettotext, inserto text, prezzo numeric, ns_fattura int, ns_fattura_data date, vs_fattura int, vs_fattura_data date, colore bool, data_prenotazione date, data_arrivo date, data_consegna date,
Re: [SQL] How to store directory like structures?
Hello! > I have no experience with those beasts, but what's wrong just untar source, > configure and compile by hand ? + Testing and maybe reinstall all stuff depending on posgressql on that server every release you want to go with ;-) Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to store directory like structures?
Hello! > On gentoo (at least on my box) it's installed by default in 8.0, I > believe it was installed by default, too, on 7.4.X Tried with gentoo just under 7.4.x and 8.0.1: axel=# CREATE TABLE test ( path ltree); ERROR: type "ltree" does not exist Any idea? Thanks, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to store directory like structures?
On Sun, 3 Apr 2005, Axel Straschil wrote: Hello! On gentoo (at least on my box) it's installed by default in 8.0, I believe it was installed by default, too, on 7.4.X Tried with gentoo just under 7.4.x and 8.0.1: axel=# CREATE TABLE test ( path ltree); ERROR: type "ltree" does not exist Any idea? you need to load ltree into your database ! psql yourdb < ltree.sql use 'locate ltree.sql' to find if ltree is installed Thanks, AXEL. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to store directory like structures?
you need to load ltree into your database ! psql yourdb < ltree.sql use 'locate ltree.sql' to find if ltree is installed Yeah, I remember now having to do that, but the binary module definitely was here without having to do anything besides "emerge postgresql" : [EMAIL PROTECTED] peufeu $ locate ltree.so /usr/lib/postgresql/ltree.so [EMAIL PROTECTED] peufeu $ locate ltree.sql /usr/share/postgresql/contrib/ltree.sql So just : psql yourdb < /usr/share/postgresql/contrib/ltree.sql And you should be OK. Really this module is amazing. You can put a trigger on the table so that, for instance, the path is created automatically from the element name and the parent_id, that kind of things. I remember search was also really fast and you can use complex queries without CONNECT BY... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Date/Time Conversion
James G Wilkinson <[EMAIL PROTECTED]> writes: > I hope that this is some silly beginner's mistake. I have spent quite a bit > of > time > reading the PostgreSQL documentation and cannot find my error. I have also > scanned the PostgreSQL archive and the web for help, but I have not found > anything > to get me over the hump (it is probably out there, I just cannot find it). > > All I am trying to do is convert GMT dates and times to an arbitrary > time zone. Here is my small test database: I think the closest thing to what you're trying to do is: slo=> set timezone = 'GMT'; SET slo=> select '2001-01-01'::date::timestamptz at time zone 'EST' ; timezone - 2000-12-31 19:00:00 (1 row) Why are you not just storing a "timestamp with timezone" with the actual time of the event, and a second column with the local time zone in which you can choose to use to display the time? -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to store directory like structures?
Hello! > psql yourdb < /usr/share/postgresql/contrib/ltree.sql *STRIKE*, thanks, works perfektyl now! Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10 ---(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] How to store directory like structures?
Hello! > psql yourdb < /usr/share/postgresql/contrib/ltree.sql *STRIKE* ... I'm a lucky guy now ;-) Thanks to all! Lg, AXEL. -- "Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in http://www.informatik-forum.at/showpost.php?p=206342&postcount=10 ---(end of broadcast)--- TIP 3: 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: [SQL] a very big table
PFC <[EMAIL PROTECTED]> writes: > Here you don't need to LEFT JOIN, you can use a straight simple > unconstrained join because the rows generated by the LEFT JOINs which have > NULL in the right columns will be rejected by the WHERE clause anyway : In recent versions of Postgres, the planner knows this and can make the simplification for itself, at least if the operator or function used in WHERE is properly marked as strict. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
