Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
On 1/29/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> Vlad <[EMAIL PROTECTED]> writes:
> > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6.
>
> The particular case you are showing here seems to be all about the speed
> of hash aggregation --- at least the time differential is mostly in the
> HashAggregate step. What is the data type of a_id? I speculate that
> you're noticing the slightly slower/more complicated hash function that
> 8.3 uses for integers. On a case where the data was well distributed
> you'd not see any countervailing efficiency gain from those extra
> cycles.
AFAIK we have a plan to update string hash in 8.4 to fastest
available (Jenkins lookup3). Maybe we should update integer
hash too then to the best:
http://www.cris.com/~Ttwang/tech/inthash.htm
("32 bit Mix Functions" is the one).
--
marko
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
On Mon, 28 Jan 2008, Tom Lane wrote: I speculate that you're noticing the slightly slower/more complicated hash function that 8.3 uses for integers. There was a similar slowdown in the Clodaldo case you tracked down recently. Is it worth considering an addition to the release notes warning about this class of problem? If there have been two of them so far just in the beta I wonder how many people are going to run into some variant of this in the future. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
Vlad <[EMAIL PROTECTED]> writes: > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6. The particular case you are showing here seems to be all about the speed of hash aggregation --- at least the time differential is mostly in the HashAggregate step. What is the data type of a_id? I speculate that you're noticing the slightly slower/more complicated hash function that 8.3 uses for integers. On a case where the data was well distributed you'd not see any countervailing efficiency gain from those extra cycles. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] using = in a function
Andy Colson <[EMAIL PROTECTED]> writes: > An assignment of a value to a PL/pgSQL variable or row/record field is > written as: > variable := expression; > Notice I just use = and not :=. > My question is, is there a difference? It seems to work both ways, so > I'm a little confused. Yeah, plpgsql actually allows both, but it's not documented. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Table has duplicate keys, what did I do
John Gateley <[EMAIL PROTECTED]> writes: > Somehow I have managed to have two tables with duplicate keys. > ... > But, mostly, I'm wondering how I managed to get in this state, What PG version is this? We've fixed some bugs in the past that could give rise to duplicated rows. regards, tom lane ---(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: [OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 20:57 -0500, Greg Smith wrote: > On Tue, 29 Jan 2008, Ow Mun Heng wrote: > > > Can you let me know what is the sql used to generate such a nice summary > > of the tables? > > Might as well dupe the old text; this went out to the performance list: > > Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can > grab at http://bucardo.org/nagios_postgres/ , and while that is itself > nice the thing I found most remarkable is the bloat check. The majority of > that code is an impressive bit of SQL that anyone could use even if you > have no interest in Nagios, which is why I point it out for broader > attention. Look in check_postgres.pl for the "check_bloat" routine and the > big statement starting at the aptly labled "This was fun to write" > section. If you pull that out of there and replace $MINPAGES and > $MINIPAGES near the end with real values, you can pop that into a > standalone query and execute it directly. I'm subscribed to perf list and I _did_ take a look at the tool previously. However, something happened and I didn't managed to look at it throughly or something. I'll take another look at it and thanks for the pointers.. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [OT] Re: [GENERAL] enabling autovacuum
On Tue, 29 Jan 2008, Ow Mun Heng wrote: Can you let me know what is the sql used to generate such a nice summary of the tables? Might as well dupe the old text; this went out to the performance list: Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can grab at http://bucardo.org/nagios_postgres/ , and while that is itself nice the thing I found most remarkable is the bloat check. The majority of that code is an impressive bit of SQL that anyone could use even if you have no interest in Nagios, which is why I point it out for broader attention. Look in check_postgres.pl for the "check_bloat" routine and the big statement starting at the aptly labled "This was fun to write" section. If you pull that out of there and replace $MINPAGES and $MINIPAGES near the end with real values, you can pop that into a standalone query and execute it directly. That's what gives the summary Jeremy included in his message. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
[OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 22:17 +, Jeremy Harris wrote: > We have one problematic table, which has a steady stream of entries > and a weekly mass-delete of ancient history. The "bloat" query from > Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: > > schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages > | wastedbytes | wastedsize |iname| ituples | ipages | > iotta | ibloat | wastedipages | wastedibytes | wastedisize > ++---+--+--++-+-++-+-++---++--+--+- > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 > | 1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | > 3819 |1.8 | 2979 | 24403968 | 23 MB > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 > | 1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 | > 3819 |1.2 | 908 | 7438336 | 7264 kB > Can you let me know what is the sql used to generate such a nice summary of the tables? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] PostgreSQL professionals group at LinkedIn.com
Decibel! wrote: > On Tue, Jan 22, 2008 at 08:20:30PM +0100, Gevik Babakhani wrote: > > Dear all, > > > > I have created a group for PostgreSQL professionals at LinkedIn.com > > Feel free to join if you like. > > > > http://www.linkedin.com/e/gis/51776/760A11717C03 > > How is that different than the existing Postgres group? Is there an existing Postgres group? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] suggested wording improvement in psql
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Harald Armin Massa wrote: > hello, > > within pgsql in \? the command help there is: > > \du [PATTERN] list users > > the answer of "list users" is > > > List of roles > > (since the introduction of rules), so I recommend updating the documentation > to > > \du [PATTERN] list roles (users) > > > Harald > > > -- > GHUM Harald Massa > persuadere et programmare > Harald Armin Massa > Spielberger Stra?e 49 > 70435 Stuttgart > 0173/9409607 > fx 01212-5-13695179 > - > EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! > > ---(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 -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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
[GENERAL] Autovacuum and stats_row_level
Noted that to use autovacuum we need to turn on stats_row_level (along with stats_start_collector that is on by default). Since stats_row_level is off by default, I wonder what sort of overhead is incurred since it sounds like it could add up if it's storing additional stats information on every row update, though it's not clear to me just what it means to have row level stats. We currently use cron to run vacuum and analyze daily in the early morning. It's never been an issue, but the databases are getting bigger and we wonder if autovacuum is a good addition to our operations. Are the default values for autovacuum generally good enough for most deployments? The various parameters are a bit complicated to understand, so tweaking them is something we're naturally concerned about doing. Will autovacuum running allow regular vacuum and analyze commands to run faster? Can it replace them entirely, or do we still need to run them from time to time? Can autovacuum be configured to run in a backup server that is in "recovery" mode handling pg_standby WAL file updates? Thanks, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] enabling autovacuum
Christopher Browne wrote: Is it possible that this table didn't see many updates, today? Nope; about 24000 (according to the id sequence). - Jeremy ---(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
Re: [GENERAL] enabling autovacuum
On Jan 28, 2008 10:17 PM, Jeremy Harris <[EMAIL PROTECTED]> wrote: > Hi, > > We're starting to run autovacuum for the first time on a system > that's been running with nightly cron-driven vacuum for some time. > > Version: > PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 > 20070418 (Red Hat 4.1.2-10) > > We have one problematic table, which has a steady stream of entries > and a weekly mass-delete of ancient history. The "bloat" query from > Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: > > schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages > | wastedbytes | wastedsize |iname| ituples | ipages | > iotta | ibloat | wastedipages | wastedibytes | wastedisize > ++---+--+--++-+-++-+-++---++--+--+- > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 > | 1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | > 3819 |1.8 | 2979 | 24403968 | 23 MB > public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 > | 1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 | > 3819 |1.2 | 908 | 7438336 | 7264 kB > We have uncommented "autovacuum = on" in postgresql.conf and run > "service postgresql reload". pg_stat_all_tables shows 4 tables > as autoanalyzed at about that time; 3 of which were also > autovacuumed. The problem table is not included; no other autos > are logged there in the succeeding 24 hours. > Is other action needed to enable autovacuum? > > > The autovacuum tuning parameters are all at default settings. > We have > max_fsm_pages = 200 > max_fsm_relations = 10 ... > Are there any other changes we should make to stop this table > getting so bloated? Is it possible that this table didn't see many updates, today? You could add an entry to pg_catalog.pg_autovacuum to customize the handling of your Favorite Table. http://www.postgresql.org/docs/8.2/static/catalog-pg-autovacuum.html You might lower the thresholds for that table... -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Table has duplicate keys, what did I do
for now, are you able to insert duplicate keys(primary-key) into the two tables you mentioned? if you can, check if your index is valid or not. if index is valid, check if the unique contraint is still valid or not -- perhaps you turned off the unique constraint, and insert the dup key, and didn't turn back on the unique constrain validation... JF -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Gateley Sent: Monday, January 28, 2008 2:26 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Table has duplicate keys, what did I do On Mon, 28 Jan 2008 14:11:21 -0800 "Dann Corbit" <[EMAIL PROTECTED]> wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > [EMAIL PROTECTED] On Behalf Of John Gateley > > Sent: Monday, January 28, 2008 2:04 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] Table has duplicate keys, what did I do > > > > Somehow I have managed to have two tables with duplicate keys. > > In both tables, the key is an integer, filled from a sequence. > > There is only 1 duplicated entry in each table: in the first table, > > there are two ID "1"s, and in the second table there are two ID > > "123456"s (the second table entry is linked to the first table's ID > > 1). > > Because of the nature of the values of the id's (1 and 123456) it > sounds very much like a manual insertion. Is there a unique index on > the column? It definitely sounds like there should be. At any rate, > I guess that someone manually inserted the data. Without a unique > index on the column, there is no protection against this. Yes, the id 1 definitely indicates to me that I did something. However, there is an index on the column: it's the primary key for the table. I'm not sure how I could manually insert it if there were an existing index, or later create the index if it didn't exist when I did the insert. Thanks, j -- John Gateley <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
> This last bit often means there's some overhead in the systems > timeofday() function calls. > > If you just use \timing from psql, and run the script without explain > analyze, what speeds do you get on each? > 17480ms (8.2.6) 20342ms (8.3RC2) -- Vlad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
On Jan 28, 2008 3:56 PM, Vlad <[EMAIL PROTECTED]> wrote: > Hello, > > 1. Freshly imported DB size on disk was about 3% smaller for 8.3 > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6. > We took special measures to make sure that no third factors involved > (no other apps running, all data was cached from disks, etc). Below > is one of the queries that we used for testing (I anonymized table > names) along with query plan for both 8.3 and 8.2. The query execution > plans are the same for both versions, but what we found quite > interesting is that if we add all the times from each line of 8.2's > query plan, it roughly adds-up to the total execution time. For 8.3's > plan each line shows a shorter time, yet resulting in longer total > runtime. Also, summing 8.3's plan lines doesn't come close to the > total execution time: This last bit often means there's some overhead in the systems timeofday() function calls. If you just use \timing from psql, and run the script without explain analyze, what speeds do you get on each? ---(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
Re: [GENERAL] Table has duplicate keys, what did I do
On Jan 28, 2008 4:26 PM, John Gateley <[EMAIL PROTECTED]> wrote: > On Mon, 28 Jan 2008 14:11:21 -0800 > "Dann Corbit" <[EMAIL PROTECTED]> wrote: > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > > [EMAIL PROTECTED] On Behalf Of John Gateley > > > Sent: Monday, January 28, 2008 2:04 PM > > > To: pgsql-general@postgresql.org > > > Subject: [GENERAL] Table has duplicate keys, what did I do > > > > > > Somehow I have managed to have two tables with duplicate keys. > > > In both tables, the key is an integer, filled from a sequence. > > > There is only 1 duplicated entry in each table: in the first > > > table, there are two ID "1"s, and in the second table there are > > > two ID "123456"s (the second table entry is linked to the first > > > table's ID 1). > > > > Because of the nature of the values of the id's (1 and 123456) it sounds > > very much like a manual insertion. Is there a unique index on the > > column? It definitely sounds like there should be. At any rate, I > > guess that someone manually inserted the data. Without a unique index > > on the column, there is no protection against this. > > Yes, the id 1 definitely indicates to me that I did something. > However, there is an index on the column: it's the primary key > for the table. I'm not sure how I could manually insert it if > there were an existing index, or later create the index if it > didn't exist when I did the insert. Are you running with fsync=off and / or hardware that lies about fsync (ATA / SATA are notorious for this) and possibly having an emergency power outage of some kind? That's the most common cause of such problems. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
Pavel: thanks for your feedback. To me plans generated by 8.2 and 8.3 are equal and only differ by execution times. (I don't know, maybe email wrap'ed lines, so I've attached plans to my message). Also, I confirm that that parameter was increased (to 100) before the ran tests. On Jan 28, 2008 4:26 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > Hello > > 8.3 plan is not optimal. > > >-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) > > (actual time=76.188..8177.510 rows=2593557 loops=1) > > please, try to increase statistics > > default_statistics_target (in postgresql.conf) to 100 and repeat > import and your test. > > Regards > Pavel Stehule -- Vlad QUERY PLAN HashAggregate (cost=134877.04..134899.04 rows=200 width=52) (actual time=21517.837..21517.890 rows=47 loops=1) -> Hash Join (cost=2450.09..111489.75 rows=550289 width=52) (actual time=76.083..7691.579 rows=2593557 loops=1) Hash Cond: (_.i_id = __3.id) -> Hash Join (cost=19.20..95377.74 rows=934651 width=56) (actual time=0.119..4933.928 rows=2596942 loops=1) Hash Cond: (_.a_id = __1.id) -> Append (cost=0.00..76276.09 rows=2596252 width=56) (actual time=0.014..2988.950 rows=2596942 loops=1) -> Seq Scan on t_c _ (cost=0.00..21.10 rows=4 width=56) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Seq Scan on t_c_2008_01 _ (cost=0.00..76254.99 rows=2596248 width=56) (actual time=0.011..1979.606 rows=2596942 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Hash (cost=18.30..18.30 rows=72 width=4) (actual time=0.094..0.094 rows=72 loops=1) -> Seq Scan on t_a __1 (cost=0.00..18.30 rows=72 width=4) (actual time=0.004..0.067 rows=72 loops=1) Filter: (status IS TRUE) -> Hash (cost=1950.44..1950.44 rows=38436 width=4) (actual time=75.931..75.931 rows=59934 loops=1) -> Hash Join (cost=57.45..1950.44 rows=38436 width=4) (actual time=0.829..54.760 rows=59934 loops=1) Hash Cond: (__3.b_id = __2.id) -> Seq Scan on t_i __3 (cost=0.00..1263.82 rows=65282 width=8) (actual time=0.005..16.852 rows=65282 loops=1) -> Hash (cost=50.90..50.90 rows=524 width=4) (actual time=0.499..0.499 rows=524 loops=1) -> Seq Scan on t_b __2 (cost=0.00..50.90 rows=524 width=4) (actual time=0.004..0.312 rows=524 loops=1) Filter: (status IS TRUE) Total runtime: 21518.097 ms QUERY PLAN HashAggregate (cost=132352.98..132572.98 rows=200 width=52) (actual time=24354.972..24355.019 rows=47 loops=1) -> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) (actual time=76.188..8177.510 rows=2593557 loops=1) Hash Cond: (_.i_id = __3.id) -> Hash Join (cost=16.20..92904.25 rows=935090 width=56) (actual time=0.140..5304.968 rows=2596942 loops=1) Hash Cond: (_.a_id = __1.id) -> Append (cost=0.00..73796.62 rows=2597473 width=56) (actual time=0.043..3272.024 rows=2596942 loops=1) -> Seq Scan on t_c _ (cost=0.00..21.55 rows=4 width=56) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Seq Scan on t_c_2008_01 _ (cost=0.00..73775.07 rows=2597469 width=56) (actual time=0.040..2245.209 rows=2596942 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Hash (cost=15.30..15.30 rows=72 width=4) (actual time=0.091..0.091 rows=72 loops=1) -> Seq Scan on t_a __1 (cost=0.00..15.30 rows=72 width=4) (actual time=0.003..0.061 rows=72 loops=1) Filter: (status IS TRUE) -> Hash (cost=1882.44..1882.44 rows=38436 width=4) (actual time=76.027..76.027 rows=59934 loops=1) -> Hash Join (cost=55.45..1882.44 rows=38436 width=4) (actual time=0.835..54.576 rows=59934 loops=1) Hash Cond: (__3.b_id = __2.id) ->
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
On 28/01/2008, Pavel Stehule <[EMAIL PROTECTED]> wrote: > Hello > > 8.3 plan is not optimal. > > >-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) > > (actual time=76.188..8177.510 rows=2593557 loops=1) > > please, try to increase statistics I am blind, I am sorry, It's noise, you did it. > > default_statistics_target (in postgresql.conf) to 100 and repeat > import and your test. > > Regards > Pavel Stehule > > On 28/01/2008, Vlad <[EMAIL PROTECTED]> wrote: > > Hello, > > > > I wanted to share performance-related test results for Postgresql > > 8.3RC2 and 8.2.6. In both cases we used a freshly imported database > > followed by analyze verbose command. Same server was used for testing > > (2.6.23.14-107.fc8 x86_64) for each versions; both postgreses were > > compiled with "-O3 -mtune=core2 -march=core2 -m64" flags. In both > > cases default configuration was used with increased shared buffers to > > 1Gb (total server ram is 32Gb), increased work and maintenance mem, > > enabled autovacuum, increased default_statistics_target to 100, > > increased effective_cache_size to 20Gb, disabled fsync and increased > > checkpoint_segments. Total size (on disk) of the tables involved in > > the query was around 300Mb. > > > > 1. Freshly imported DB size on disk was about 3% smaller for 8.3 > > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6. > > We took special measures to make sure that no third factors involved > > (no other apps running, all data was cached from disks, etc). Below > > is one of the queries that we used for testing (I anonymized table > > names) along with query plan for both 8.3 and 8.2. The query execution > > plans are the same for both versions, but what we found quite > > interesting is that if we add all the times from each line of 8.2's > > query plan, it roughly adds-up to the total execution time. For 8.3's > > plan each line shows a shorter time, yet resulting in longer total > > runtime. Also, summing 8.3's plan lines doesn't come close to the > > total execution time: > > > > SELECT _."a_id", SUM(_."counter") > > FROM ts.t_c AS _ > > LEFT OUTER JOIN ts.t_a AS __1 ON _."a_id"=__1."id" > > LEFT OUTER JOIN ts.t_i AS __3 ON _."i_id"=__3."id" > > LEFT OUTER JOIN ts.t_b AS __2 ON __3."b_id"=__2."id" > > WHERE (_."date_day" >= '2008-01-01 00:00:00' AND _."date_day" <= > > '2008-01-27 23:59:59') > > AND __1."status" IS TRUE > > AND __2."status" IS TRUE > > GROUP BY _."a_id" > > > > > > 8.2.6 QUERY PLAN > > > > HashAggregate (cost=134877.04..134899.04 rows=200 width=52) (actual > > time=21517.837..21517.890 rows=47 loops=1) > >-> Hash Join (cost=2450.09..111489.75 rows=550289 width=52) > > (actual time=76.083..7691.579 rows=2593557 loops=1) > > Hash Cond: (_.i_id = __3.id) > > -> Hash Join (cost=19.20..95377.74 rows=934651 width=56) > > (actual time=0.119..4933.928 rows=2596942 loops=1) > >Hash Cond: (_.a_id = __1.id) > >-> Append (cost=0.00..76276.09 rows=2596252 width=56) > > (actual time=0.014..2988.950 rows=2596942 loops=1) > > -> Seq Scan on t_c _ (cost=0.00..21.10 rows=4 > > width=56) (actual time=0.001..0.001 rows=0 loops=1) > >Filter: ((date_day >= '2008-01-01'::date) > > AND (date_day <= '2008-01-27'::date)) > > -> Seq Scan on t_c_2008_01 _ > > (cost=0.00..76254.99 rows=2596248 width=56) (actual > > time=0.011..1979.606 rows=2596942 loops=1) > >Filter: ((date_day >= '2008-01-01'::date) > > AND (date_day <= '2008-01-27'::date)) > >-> Hash (cost=18.30..18.30 rows=72 width=4) (actual > > time=0.094..0.094 rows=72 loops=1) > > -> Seq Scan on t_a __1 (cost=0.00..18.30 > > rows=72 width=4) (actual time=0.004..0.067 rows=72 loops=1) > >Filter: (status IS TRUE) > > -> Hash (cost=1950.44..1950.44 rows=38436 width=4) (actual > > time=75.931..75.931 rows=59934 loops=1) > >-> Hash Join (cost=57.45..1950.44 rows=38436 width=4) > > (actual time=0.829..54.760 rows=59934 loops=1) > > Hash Cond: (__3.b_id = __2.id) > > -> Seq Scan on t_i __3 (cost=0.00..1263.82 > > rows=65282 width=8) (actual time=0.005..16.852 rows=65282 loops=1) > > -> Hash (cost=50.90..50.90 rows=524 width=4) > > (actual time=0.499..0.499 rows=524 loops=1) > >-> Seq Scan on t_b __2 (cost=0.00..50.90 > > rows=524 width=4) (actual time=0.004..0.312 rows=524 loops=1) > > Filter: (status IS TRUE) > > Total runtime: 21518.097 ms > > > > > > > > 8.3RC2: QUERY PLAN > > ---
Re: [GENERAL] 8.3RC2 vs 8.2.6 testing results
Hello 8.3 plan is not optimal. >-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) > (actual time=76.188..8177.510 rows=2593557 loops=1) please, try to increase statistics default_statistics_target (in postgresql.conf) to 100 and repeat import and your test. Regards Pavel Stehule On 28/01/2008, Vlad <[EMAIL PROTECTED]> wrote: > Hello, > > I wanted to share performance-related test results for Postgresql > 8.3RC2 and 8.2.6. In both cases we used a freshly imported database > followed by analyze verbose command. Same server was used for testing > (2.6.23.14-107.fc8 x86_64) for each versions; both postgreses were > compiled with "-O3 -mtune=core2 -march=core2 -m64" flags. In both > cases default configuration was used with increased shared buffers to > 1Gb (total server ram is 32Gb), increased work and maintenance mem, > enabled autovacuum, increased default_statistics_target to 100, > increased effective_cache_size to 20Gb, disabled fsync and increased > checkpoint_segments. Total size (on disk) of the tables involved in > the query was around 300Mb. > > 1. Freshly imported DB size on disk was about 3% smaller for 8.3 > 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6. > We took special measures to make sure that no third factors involved > (no other apps running, all data was cached from disks, etc). Below > is one of the queries that we used for testing (I anonymized table > names) along with query plan for both 8.3 and 8.2. The query execution > plans are the same for both versions, but what we found quite > interesting is that if we add all the times from each line of 8.2's > query plan, it roughly adds-up to the total execution time. For 8.3's > plan each line shows a shorter time, yet resulting in longer total > runtime. Also, summing 8.3's plan lines doesn't come close to the > total execution time: > > SELECT _."a_id", SUM(_."counter") > FROM ts.t_c AS _ > LEFT OUTER JOIN ts.t_a AS __1 ON _."a_id"=__1."id" > LEFT OUTER JOIN ts.t_i AS __3 ON _."i_id"=__3."id" > LEFT OUTER JOIN ts.t_b AS __2 ON __3."b_id"=__2."id" > WHERE (_."date_day" >= '2008-01-01 00:00:00' AND _."date_day" <= > '2008-01-27 23:59:59') > AND __1."status" IS TRUE > AND __2."status" IS TRUE > GROUP BY _."a_id" > > > 8.2.6 QUERY PLAN > > HashAggregate (cost=134877.04..134899.04 rows=200 width=52) (actual > time=21517.837..21517.890 rows=47 loops=1) >-> Hash Join (cost=2450.09..111489.75 rows=550289 width=52) > (actual time=76.083..7691.579 rows=2593557 loops=1) > Hash Cond: (_.i_id = __3.id) > -> Hash Join (cost=19.20..95377.74 rows=934651 width=56) > (actual time=0.119..4933.928 rows=2596942 loops=1) >Hash Cond: (_.a_id = __1.id) >-> Append (cost=0.00..76276.09 rows=2596252 width=56) > (actual time=0.014..2988.950 rows=2596942 loops=1) > -> Seq Scan on t_c _ (cost=0.00..21.10 rows=4 > width=56) (actual time=0.001..0.001 rows=0 loops=1) >Filter: ((date_day >= '2008-01-01'::date) > AND (date_day <= '2008-01-27'::date)) > -> Seq Scan on t_c_2008_01 _ > (cost=0.00..76254.99 rows=2596248 width=56) (actual > time=0.011..1979.606 rows=2596942 loops=1) >Filter: ((date_day >= '2008-01-01'::date) > AND (date_day <= '2008-01-27'::date)) >-> Hash (cost=18.30..18.30 rows=72 width=4) (actual > time=0.094..0.094 rows=72 loops=1) > -> Seq Scan on t_a __1 (cost=0.00..18.30 > rows=72 width=4) (actual time=0.004..0.067 rows=72 loops=1) >Filter: (status IS TRUE) > -> Hash (cost=1950.44..1950.44 rows=38436 width=4) (actual > time=75.931..75.931 rows=59934 loops=1) >-> Hash Join (cost=57.45..1950.44 rows=38436 width=4) > (actual time=0.829..54.760 rows=59934 loops=1) > Hash Cond: (__3.b_id = __2.id) > -> Seq Scan on t_i __3 (cost=0.00..1263.82 > rows=65282 width=8) (actual time=0.005..16.852 rows=65282 loops=1) > -> Hash (cost=50.90..50.90 rows=524 width=4) > (actual time=0.499..0.499 rows=524 loops=1) >-> Seq Scan on t_b __2 (cost=0.00..50.90 > rows=524 width=4) (actual time=0.004..0.312 rows=524 loops=1) > Filter: (status IS TRUE) > Total runtime: 21518.097 ms > > > > 8.3RC2: QUERY PLAN > > HashAggregate (cost=132352.98..132572.98 rows=200 width=52) (actual > time=24354.972..24355.019 rows=47 loops=1) >-> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) > (actual time=76.188..8177.510 rows=2
Re: [GENERAL] Table has duplicate keys, what did I do
On Mon, 28 Jan 2008 14:11:21 -0800 "Dann Corbit" <[EMAIL PROTECTED]> wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > [EMAIL PROTECTED] On Behalf Of John Gateley > > Sent: Monday, January 28, 2008 2:04 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] Table has duplicate keys, what did I do > > > > Somehow I have managed to have two tables with duplicate keys. > > In both tables, the key is an integer, filled from a sequence. > > There is only 1 duplicated entry in each table: in the first > > table, there are two ID "1"s, and in the second table there are > > two ID "123456"s (the second table entry is linked to the first > > table's ID 1). > > Because of the nature of the values of the id's (1 and 123456) it sounds > very much like a manual insertion. Is there a unique index on the > column? It definitely sounds like there should be. At any rate, I > guess that someone manually inserted the data. Without a unique index > on the column, there is no protection against this. Yes, the id 1 definitely indicates to me that I did something. However, there is an index on the column: it's the primary key for the table. I'm not sure how I could manually insert it if there were an existing index, or later create the index if it didn't exist when I did the insert. Thanks, j -- John Gateley <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] using = in a function
Hi all, I was reading the doc's on functions when I came across this: " An assignment of a value to a PL/pgSQL variable or row/record field is written as: variable := expression; " and I realized, I didn't do that! My assignments look like: tmp = extract(minute from result); if tmp > 30 then tmp = 60 - tmp; offset = tmp || ' minutes'; result = result + offset; else offset = tmp || ' minutes'; result = result - offset; end if; Notice I just use = and not :=. My question is, is there a difference? It seems to work both ways, so I'm a little confused. Thanks, -Andy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] enabling autovacuum
Hi,
We're starting to run autovacuum for the first time on a system
that's been running with nightly cron-driven vacuum for some time.
Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070418 (Red Hat 4.1.2-10)
We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The "bloat" query from
Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns:
schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize |iname| ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize
++---+--+--++-+-++-+-++---++--+--+-
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 |
1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 |
3819 |1.8 | 2979 | 24403968 | 23 MB
public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 |
1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 |
3819 |1.2 | 908 | 7438336 | 7264 kB
The table description is:
id | integer | not null default nextval(('"rcpt_audit_id_seq"'::text)::regclass) |
msg_audit_id | integer | |
mailuser_id | integer | |
username | text| |
domain | text| |
copies | integer | |
end_msg_size | integer | |
disp_type| integer | |
disp_id | integer | |
disp_action | text| |
disposition | text| |
hdrs | text| |
We have uncommented "autovacuum = on" in postgresql.conf and run
"service postgresql reload". pg_stat_all_tables shows 4 tables
as autoanalyzed at about that time; 3 of which were also
autovacuumed. The problem table is not included; no other autos
are logged there in the succeeding 24 hours.
Is other action needed to enable autovacuum?
The autovacuum tuning parameters are all at default settings.
We have
max_fsm_pages = 200
max_fsm_relations = 10
Are there any other changes we should make to stop this table
getting so bloated?
Thanks,
Jeremy
---(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
Re: [GENERAL] Table has duplicate keys, what did I do
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of John Gateley > Sent: Monday, January 28, 2008 2:04 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Table has duplicate keys, what did I do > > Somehow I have managed to have two tables with duplicate keys. > In both tables, the key is an integer, filled from a sequence. > There is only 1 duplicated entry in each table: in the first > table, there are two ID "1"s, and in the second table there are > two ID "123456"s (the second table entry is linked to the first > table's ID 1). Because of the nature of the values of the id's (1 and 123456) it sounds very much like a manual insertion. Is there a unique index on the column? It definitely sounds like there should be. At any rate, I guess that someone manually inserted the data. Without a unique index on the column, there is no protection against this. > I noticed this because a pg_dump followed by a psql < dumpfile > will not reload. > > I've figured out a fix: a script that cleans the dump file, removing > the two duplicate lines (leaving the original). > > But, mostly, I'm wondering how I managed to get in this state, > if it was something I did, or perhaps caused by killing the > postmaster the wrong way (I don't think I ever did this, but > maybe), or a crash. > > I did do a brief search, didn't find anything seemingly related to this. > > Thanks, > > j > -- > John Gateley <[EMAIL PROTECTED]> > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Table has duplicate keys, what did I do
Somehow I have managed to have two tables with duplicate keys. In both tables, the key is an integer, filled from a sequence. There is only 1 duplicated entry in each table: in the first table, there are two ID "1"s, and in the second table there are two ID "123456"s (the second table entry is linked to the first table's ID 1). I noticed this because a pg_dump followed by a psql < dumpfile will not reload. I've figured out a fix: a script that cleans the dump file, removing the two duplicate lines (leaving the original). But, mostly, I'm wondering how I managed to get in this state, if it was something I did, or perhaps caused by killing the postmaster the wrong way (I don't think I ever did this, but maybe), or a crash. I did do a brief search, didn't find anything seemingly related to this. Thanks, j -- John Gateley <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] handling of COUNT(record) vs IS NULL
On Mon, Jan 28, 2008 at 04:38:01PM -0500, Tom Lane wrote: > Sam Mason <[EMAIL PROTECTED]> writes: > > I've just noticed that the handling of COUNT(record) and (record IS > > NULL) aren't consistent with my understanding of them. If I run the > > following query: > > > SELECT > > NULL IS NULL, COUNT( NULL ), > > (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > > > The IS NULL checks both return TRUE as I'd expect them to, but the > > second count doesn't return 0. > > THe fourth of those isn't really valid SQL. According to SQL99, > IS NULL takes a as argument, so it's valid > to do (NULL,NULL) IS NULL, but COUNT takes a . But isn't COUNT(*), logically from a syntactic point of view, working with records? Or is it really supposed to be a special hack. My intuition of the original intent of COUNT(*) is that all tables have at least one non-null column, so it's safe to rewrite COUNT(*) into COUNT(1). In general this doesn't seem correct to me though. > I don't see anything in the spec suggesting that we are supposed > to drill down into a rowtype value to see whether all its fields > are null, in any context other than the IS [NOT] NULL predicate. I believe that somewhere in the spec COUNT is defined to return the count of non-null rows. If so, then the behaviour of COUNT isn't consistent with IS NULL and if the spec only defines the behaviour for non-record values then you can't look to it for guidance. Wouldn't it be better to either ban counts of records or make it follow the same semantics as the IS NULL predicate. Sam ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 8.3RC2 vs 8.2.6 testing results
Hello, I wanted to share performance-related test results for Postgresql 8.3RC2 and 8.2.6. In both cases we used a freshly imported database followed by analyze verbose command. Same server was used for testing (2.6.23.14-107.fc8 x86_64) for each versions; both postgreses were compiled with "-O3 -mtune=core2 -march=core2 -m64" flags. In both cases default configuration was used with increased shared buffers to 1Gb (total server ram is 32Gb), increased work and maintenance mem, enabled autovacuum, increased default_statistics_target to 100, increased effective_cache_size to 20Gb, disabled fsync and increased checkpoint_segments. Total size (on disk) of the tables involved in the query was around 300Mb. 1. Freshly imported DB size on disk was about 3% smaller for 8.3 2. We ran several tests and found 8.3 generally 10% slower than 8.2.6. We took special measures to make sure that no third factors involved (no other apps running, all data was cached from disks, etc). Below is one of the queries that we used for testing (I anonymized table names) along with query plan for both 8.3 and 8.2. The query execution plans are the same for both versions, but what we found quite interesting is that if we add all the times from each line of 8.2's query plan, it roughly adds-up to the total execution time. For 8.3's plan each line shows a shorter time, yet resulting in longer total runtime. Also, summing 8.3's plan lines doesn't come close to the total execution time: SELECT _."a_id", SUM(_."counter") FROM ts.t_c AS _ LEFT OUTER JOIN ts.t_a AS __1 ON _."a_id"=__1."id" LEFT OUTER JOIN ts.t_i AS __3 ON _."i_id"=__3."id" LEFT OUTER JOIN ts.t_b AS __2 ON __3."b_id"=__2."id" WHERE (_."date_day" >= '2008-01-01 00:00:00' AND _."date_day" <= '2008-01-27 23:59:59') AND __1."status" IS TRUE AND __2."status" IS TRUE GROUP BY _."a_id" 8.2.6 QUERY PLAN HashAggregate (cost=134877.04..134899.04 rows=200 width=52) (actual time=21517.837..21517.890 rows=47 loops=1) -> Hash Join (cost=2450.09..111489.75 rows=550289 width=52) (actual time=76.083..7691.579 rows=2593557 loops=1) Hash Cond: (_.i_id = __3.id) -> Hash Join (cost=19.20..95377.74 rows=934651 width=56) (actual time=0.119..4933.928 rows=2596942 loops=1) Hash Cond: (_.a_id = __1.id) -> Append (cost=0.00..76276.09 rows=2596252 width=56) (actual time=0.014..2988.950 rows=2596942 loops=1) -> Seq Scan on t_c _ (cost=0.00..21.10 rows=4 width=56) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Seq Scan on t_c_2008_01 _ (cost=0.00..76254.99 rows=2596248 width=56) (actual time=0.011..1979.606 rows=2596942 loops=1) Filter: ((date_day >= '2008-01-01'::date) AND (date_day <= '2008-01-27'::date)) -> Hash (cost=18.30..18.30 rows=72 width=4) (actual time=0.094..0.094 rows=72 loops=1) -> Seq Scan on t_a __1 (cost=0.00..18.30 rows=72 width=4) (actual time=0.004..0.067 rows=72 loops=1) Filter: (status IS TRUE) -> Hash (cost=1950.44..1950.44 rows=38436 width=4) (actual time=75.931..75.931 rows=59934 loops=1) -> Hash Join (cost=57.45..1950.44 rows=38436 width=4) (actual time=0.829..54.760 rows=59934 loops=1) Hash Cond: (__3.b_id = __2.id) -> Seq Scan on t_i __3 (cost=0.00..1263.82 rows=65282 width=8) (actual time=0.005..16.852 rows=65282 loops=1) -> Hash (cost=50.90..50.90 rows=524 width=4) (actual time=0.499..0.499 rows=524 loops=1) -> Seq Scan on t_b __2 (cost=0.00..50.90 rows=524 width=4) (actual time=0.004..0.312 rows=524 loops=1) Filter: (status IS TRUE) Total runtime: 21518.097 ms 8.3RC2: QUERY PLAN HashAggregate (cost=132352.98..132572.98 rows=200 width=52) (actual time=24354.972..24355.019 rows=47 loops=1) -> Hash Join (cost=2379.09..108954.69 rows=550548 width=52) (actual time=76.188..8177.510 rows=2593557 loops=1) Hash Cond: (_.i_id = __3.id) -> Hash Join (cost=16.20..92904.25 rows=935090 width=56) (actual time=0.140..5304.968 rows=2596942 loops=1) Hash Cond: (_.a_id = __1.id) -> Append (cost=0.00..73796.62 rows=2597473 width=56) (actual time=0.043..3272.024 rows=2596942 loops=1) -> Seq Scan on t_c _ (cost=0.00..21.55 rows=4 width=56) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((date_day >= '2008-
Re: [GENERAL] handling of COUNT(record) vs IS NULL
"Tom Lane" <[EMAIL PROTECTED]> writes: > Sam Mason <[EMAIL PROTECTED]> writes: >> I've just noticed that the handling of COUNT(record) and (record IS >> NULL) aren't consistent with my understanding of them. If I run the >> following query: > >> SELECT >> NULL IS NULL, COUNT( NULL ), >> (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > >> The IS NULL checks both return TRUE as I'd expect them to, but the >> second count doesn't return 0. > > THe fourth of those isn't really valid SQL. According to SQL99, > IS NULL takes a as argument, so it's valid > to do (NULL,NULL) IS NULL, but COUNT takes a . > > I don't see anything in the spec suggesting that we are supposed > to drill down into a rowtype value to see whether all its fields > are null, in any context other than the IS [NOT] NULL predicate. Well it's not just in the predicate, we handle it for other strict operators and functions: postgres=# select (ROW(null,null)=row(1,2)) IS NULL; ?column? -- t (1 row) It does seem a bit inconsistent: postgres=# select count(ROW(null,null)=row(1,2)); count --- 0 (1 row) postgres=# select count(ROW(null,null)); count --- 1 (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] handling of COUNT(record) vs IS NULL
Sam Mason <[EMAIL PROTECTED]> writes: > I've just noticed that the handling of COUNT(record) and (record IS > NULL) aren't consistent with my understanding of them. If I run the > following query: > SELECT > NULL IS NULL, COUNT( NULL ), > (NULL,NULL) IS NULL, COUNT((NULL,NULL)); > The IS NULL checks both return TRUE as I'd expect them to, but the > second count doesn't return 0. THe fourth of those isn't really valid SQL. According to SQL99, IS NULL takes a as argument, so it's valid to do (NULL,NULL) IS NULL, but COUNT takes a . I don't see anything in the spec suggesting that we are supposed to drill down into a rowtype value to see whether all its fields are null, in any context other than the IS [NOT] NULL predicate. regards, tom lane ---(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
Re: [GENERAL] Surprising (?) Sequence Behavior
"Richard M. Kues" <[EMAIL PROTECTED]> writes:
> CREATE TEMPORARY SEQUENCE s;
> SELECT
> nextval('s'), t.name
> FROM
> (
> SELECT
>tablename AS name
> FROM
>pg_tables
> ORDER BY
>tablename
> ) AS t
> WHERE
> t.name = 'pg_am'
> ;
> The result is:
> 1 pg_am
> instead of:
> 2 pg_am
> At least for me this is surprising!
Why do you find it surprising? Per spec, the SELECT output list is not
evaluated at rows that fail the WHERE clause. This must be so; consider
examples like
SELECT 1/x FROM t WHERE x <> 0;
I think what you need is three levels of nested SELECT, with the
nextval() done in the middle level, and probably an "OFFSET 0" in the
middle one to keep Postgres from collapsing the top and middle together.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] PostgreSQL professionals group at LinkedIn.com
On Tue, Jan 22, 2008 at 08:20:30PM +0100, Gevik Babakhani wrote: > Dear all, > > I have created a group for PostgreSQL professionals at LinkedIn.com > Feel free to join if you like. > > http://www.linkedin.com/e/gis/51776/760A11717C03 How is that different than the existing Postgres group? -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgp45Bo8fd3KM.pgp Description: PGP signature
[GENERAL] Surprising (?) Sequence Behavior
Hallo all
During a performance tuning session I had a complex
query that gives some form of ranking. The "correct" way to
solve this, is the use of a scalar subquery that provides
the rank (or use "dense_rank over" in oracle).
But in my case the query is much too slow in this special case.
Even with small number of records that fit into memory (no IO).
So I'am searching for a faster solution and tried
also to use temporary sequences to achieve the same effect.
Example 1:
DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;
SELECT
nextval('s'), t.name
FROM
(
SELECT
tablename AS name
FROM
pg_tables
ORDER BY
tablename
) AS t;
gives:
1 pg_aggregate
2 pg_am
3 pg_amop
4 pg_amproc
5 pg_attrdef
6 pg_attribute
7 pg_auth_members
But if this query is combined with a simple extension it does
not work as expected.
DROP SEQUENCE IF EXISTS s;
CREATE TEMPORARY SEQUENCE s;
SELECT
nextval('s'), t.name
FROM
(
SELECT
tablename AS name
FROM
pg_tables
ORDER BY
tablename
) AS t
WHERE
t.name = 'pg_am'
;
The result is:
1 pg_am
instead of:
2 pg_am
At least for me this is surprising!
Any hints? Or do I miss something obvious?
thanks a lot, richard
---(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: [GENERAL] close connection
On 1/28/08, Dominique Bessette - Halsema <[EMAIL PROTECTED]> wrote: > > > > I dont really understand the question but here's an xml example of what is > getting posted to postgres, and i'm trying to figure out how to do a > connection.close() type of thing in it. > > Postgres doesn't have a built-in webserver, so you are obviously going through some kind of webserver/appserver to do the HTTP post. Figure out how to tell that server to close the connection. -Doug
Re: [GENERAL] Error after upgrade
"Paul Houselander" <[EMAIL PROTECTED]> writes: > $uweeklysqlu = "UPDATE stats_? SET > statdate=?,inctr=inctr+?,insize=insize+?,inspam=inspam+?,invir=invir+?,outct > r=outctr+?,outsize=outsize+?,outspam=outspam+?,outvir=outvir+? WHERE > account=? AND stattype=? AND statmode=? AND extract(week from > statdate)=extract(week from date ?) and extract(year from > statdate)=extract(year from date ?)"; "date ?" is incorrect. You got away with it before because you were using an older driver that substituted a literal constant into the query before sending it (ie, the query wasn't really prepared at all). The right way is "?::date", or more verbosely "CAST(? AS date)". See the discussions of casting and assigning types to constants in sections 4.1.2.5 and 4.2.8 of the manual. I doubt that "stats_?" as a table name is going to work either. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] close connection
i forgot to add on the xml code http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.0.0/WFS-transaction.xsd http://www.opengeospatial.net/cite http://:/geoserver/wfs/DescribeFeatureType?typename=cite:tracks,cite:alias " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:cite=" http://www.opengeospatial.net/cite"; xmlns:ogc="http://www.opengis.net/ogc"; xmlns:wfs="http://www.opengis.net/wfs"; xmlns:gml="http://www.opengis.net/gml "> < ogc:PropertyIsEqualTo> guid < ogc:Literal>TEST11108 On 1/24/08, Dominique Bessette - Halsema <[EMAIL PROTECTED]> wrote: > > I'm http posting postgres from my wfs. in otherwords i'm postgres is my > datastore and i'm posting xml to it. so do you know how to close it in xml? > > > On 1/24/08, Bill Moran <[EMAIL PROTECTED]> wrote: > > > > "Dominique Bessette - Halsema" <[EMAIL PROTECTED]> wrote: > > > > > > is there a way to close the connection in postgres after i insert > > something? > > > > > > > The short answer is "yes". > > > > In order to provide a more detailed answer, I suspect you're going to > > have > > to provide a little more context ... are you writing a C application, or > > using the psql program or something else? > > > > -- > > Bill Moran > > http://www.potentialtech.com > > > >
[GENERAL] close connection
I dont really understand the question but here's an xml example of what is getting posted to postgres, and i'm trying to figure out how to do a connection.close() type of thing in it. http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.0.0/WFS-transaction.xsd http://www.opengeospatial.net/cite http://:/geoserver/wfs/DescribeFeatureType?typename=cite:tracks,cite:alias " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:cite=" http://www.opengeospatial.net/cite"; xmlns:ogc="http://www.opengis.net/ogc"; xmlns:wfs="http://www.opengis.net/wfs"; xmlns:gml="http://www.opengis.net/gml "> < ogc:PropertyIsEqualTo> guid < ogc:Literal>TEST11108 On 1/24/08, Erik Jones <[EMAIL PROTECTED]> wrote: > > > On Jan 24, 2008, at 3:47 PM, Dominique Bessette - Halsema wrote: > > > is there a way to close the connection in postgres after i insert > > something? > > > Of course. Exactly how will be dependent on what you're using to > access the db (language/framework-wise). > > Erik Jones > > DBA | Emma(R) > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > >
Re: [GENERAL] Bug (?) in JDB
Calum, I do not know about Java and jdbc, but had similiar problems with various Python-Database adapters. Have you double-checked that true and false are valid options? at http://www.postgresql.org/docs/current/interactive/libpq-connect.html you can read sslmode This option determines whether or with what priority an SSL connection will be negotiated with the server. There are four modes: disable will attempt only an unencrypted SSL connection; allow will negotiate, trying first a non-SSL connection, then if that fails, trying an SSL connection; prefer (the default) will negotiate, trying first an SSL connection, then if that fails, trying a regular non-SSL connection; require will try only an SSL connection. If PostgreSQL is compiled without SSL support, using option require will cause an error, while options allow and prefer will be accepted but libpq will not in fact attempt an SSL connection. so probably you should use "disable" instead of "false", and "require" or "prefer" instead of true? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is news.postgresql.org down?
In article <[EMAIL PROTECTED]>, Rainer Bauer <[EMAIL PROTECTED]> writes: > Hopefully it won't be down for too long as I use a newsreader to read > the lists. I use www.gmane.org for that. ---(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
[GENERAL] create a limited user in Postgre
Hi, i have a question: I have a database and i want to create a administrator user with total control and another that only could make queries and could not see nor modify the functions. The reason is that in the database that i will distribute are the functions with a big part of the software logics and i do not want these to be seen by my customers. Thanks in advance, Guillermo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] create a limited user in Postgre
>and another that only could make queries and could not see nor >modify the functions. Up until 8.1 it is possible to see all the functions (plpgsql code) in pgAdmin, even if you don't have access rights to the schema. I'm not sure how this is in 8.2 or 8.3? WBL On Jan 22, 2008 7:02 AM, Sim Zacks <[EMAIL PROTECTED]> wrote: > That's not very open-source of you. LOL. > > Aside from database permission issues, which are not a problem, you would > also > have to not give them superuser access to their own database server > (something > that a lot of companies will not be happy with). Especially if they want > to the > possibility of other databases on the server. You would also have to > prevent > them from holding onto the backups, something that most companies would > not > tolerate at all. > > sim > > > > Guillermo Arias wrote: > > Hi, i have a question: > > > > I have a database and i want to create a administrator user with total > > control and another that only could make queries and could not see nor > > modify the functions. > > > > The reason is that in the database that i will distribute are the > > functions with a big part of the software logics and i do not want > > these to be seen by my customers. > > > > Thanks in advance, Guillermo > > ---(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 >
[GENERAL] PostgreSQL 8.3 RC2 is now available!
PostgreSQL 8.3 RC2 is now available The community testing of RC1 has yielded positive results. We avoided several nasty bugs and are now releasing 8.3 RC2. We need the entire community to continue testing to help us get to the final release. Please report your bugs before the end of the month! . You may download RC2 from the following links: http://www.postgresql.org/ftp/source/v8.3RC2/ http://www.postgresql.org/ftp/binary/v8.3RC2/ Please report any bugs you find per the instructions found on the beta testing page: http://www.postgresql.org/developer/beta Happy Testing! Joshua Drake [EMAIL PROTECTED] -- * PostgreSQL - The world's most advanced open source database & http://www.postgresql.org/ () Everything else pales in comparison ---(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: [GENERAL] (un)grouping question
Don't this satisfy your requirement? (This isn't tested. There may be some syntax error.) DELETE FROM your_table T WHERE uid > (SELECT MIN(uid) FROM your_table M WHERE M.somevalue = T.somevalue ) ; The result I expected is: SELECT * FROM your_table; uid|somevalue 1|11 3|31 6|33 2|44 5|71 ---(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
Re: [GENERAL] postgresql 8.3rc1 on vista
Yes I checked the file permissions... In fact I logged in as the user specified in the service and started postgres via the command line with postgres -D ../data and the postgres.conf parameters were loaded as confirmed with a "show all" in psql And still running postgresql from the service does not load the parameters from the postgres.conf file. So it does not seem like a permissions issue. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Bug (?) in JDB
Hello all,
I've noticed that when using Java and postgres-jdbc, setting ssl=false
in Properties doesn't get read, and ssl=false and ssl=true both cause
SSL to be tried.
E.g:
String url = "jdbc:postgresql://"+host+"/"+database;
Properties props = new Properties();
props.setProperty("user", "blah");
props.setProperty("password", "blah");
props.setProperty("ssl","false");
props.setProperty("sslfactory",
"org.postgresql.ssl.NonValidatingFactory");
Connection conn = null;
conn = DriverManager.getConnection(url, props);
causes it to throw: org.postgresql.util.PSQLException: The server does
not support SSL. (even though I've said not to try SSL).
String url = "jdbc:postgresql://"+host+"/"+database;
Properties props = new Properties();
props.setProperty("user", "blah");
props.setProperty("password", "blah");
// Don't even set these ones below, as they seem to make it
try SSL regardless.
// props.setProperty("ssl","false");
// props.setProperty("sslfactory",
"org.postgresql.ssl.NonValidatingFactory");
Connection conn = null;
conn = DriverManager.getConnection(url, props);
works fine.
Problem exists both with postgresql-8.2-505.jdbc3.jar and
postgresql-8.2-507.jdbc3.jar
C
--
bash# Got root? Get root. Fast Linux VPS
http://linuxvps.org/
---(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
[GENERAL] Tsearch2 slovak UTF-8
Hi All
I have PostgreSQL 8.2.6 running on Windows. I tryed install slovak
dictionary for tsearch2.
INSERT INTO pg_ts_dict
VALUES('ispell_slovak','spell_init(internal)','DictFile="C:/slovak_utf8.dict",
AffFile="C:/slovak_utf8.aff", StopFile="C:/slovak_utf8.stop"',
'spell_lexize(internal,internal,integer)','Slovak ISpell. UTF8 Encoding');
INSERT INTO pg_ts_cfg VALUES('utf8_slovak', 'default', 'Slovak_Slovakia');
INSERT INTO pg_ts_cfgmap
SELECT 'utf8_slovak',tok_alias,dict_name
FROM pg_ts_cfgmap WHERE ts_name='utf8_russian';
UPDATE pg_ts_cfgmap SET dict_name='{ispell_slovak,simple}'
WHERE ('ru_stem'=ANY(dict_name) OR 'ru_stem_utf8'=ANY(dict_name) OR
'en_stem' = ANY(dict_name))
AND ts_name='utf8_slovak';
There are few problems.
1. It's not possible use path DictFile="C:/Program
Files/PostgreSQL/8.2/slovak_utf8.dict"
2. I always got "ERROR: Affix parse error at 63 line
In statement:
SELECT to_tsvector('utf8_slovak', 'žltá vody');"
Second problem is most important. On that line is first UTF8 character. I
read that it's necessary install patch tsearch_snowball_82.gz. But I am
running under windows and I am not so skill for windows compilation. I
thought that lastest PostgreSQL version has this patch included.
Thanks for help.
Jan Sunavec
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] handling of COUNT(record) vs IS NULL
Hi, I've just noticed that the handling of COUNT(record) and (record IS NULL) aren't consistent with my understanding of them. If I run the following query: SELECT NULL IS NULL, COUNT( NULL ), (NULL,NULL) IS NULL, COUNT((NULL,NULL)); The IS NULL checks both return TRUE as I'd expect them to, but the second count doesn't return 0. The Comparison Operator docs[1] describe the behaviour of IS NULL changing, with respect to records, in version 8.2. Is count still exhibiting the old behaviour? Sam [1] http://www.postgresql.org/docs/8.2/static/functions-comparison.html ---(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
Re: [GENERAL] A select DISTINCT query? - followup Q
"Harald Fuchs" <[EMAIL PROTECTED]> writes: > If you want to select both columns, but have uniqueness over the first > only, you can use a derived table: > > SELECT tbl.name, tbl.comment > FROM tbl > JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t > ON t.name = tbl.name > Or use the first() aggregate since you know there's only going to be one anyways: select name, first(comment) from tbl group by name having count(*) = 1 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is news.postgresql.org down?
Hello Magnus, MH> Yes, it's been down for quite a long time. AFAIK, Marc has a plan for MH> fixing it, but I don't know the timeframe. Thanks Magnus. I was not sure whether it was really the server. Hopefully it won't be down for too long as I use a newsreader to read the lists. Rainer ---(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: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
On Mon, Jan 28, 2008 at 03:11:10PM +0100, H??kan Jacobsson wrote:
> Resulting in 4 columns in the ResultSet like:
>
> count(*)_from_table2_between_fromdate1_and_todate1 = X
> count(*)_from_table2_between_fromdate2_and_todate2 = Y
> count(*)_from_table3_between_fromdate1_and_todate1 = Z
> count(*)_from_table3_between_fromdate2_and_todate2 = V
>
> Is this possible?
How about something like:
SELECT x.tbl, d.mn, COUNT(*)
FROM (
SELECT 'tbl2' AS tbl, id, date FROM table2
UNION ALL
SELECT 'tbl3', id, date FROM table3) x, (VALUES
('2001-1-1','2001-12-31'),
('2002-1-1','2002-12-31')) d(mn,mx)
WHERE x.date BETWEEN d.mn AND d.mx
AND x.id IN (5,6,7,8)
GROUP BY x.tbl, d.mn
ORDER BY x.tbl, d.mn;
Sam
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
Håkan Jacobsson wrote: > I don't get it=). How do I input the second daterange in this query? Through whatever mechanism you are using to issue the query. > Also, I have the ID from table1. Its known in the query. Oops. I forgot that part in my reply. So my where clause is wrong, though easily modified to add "and table1_id = foo". > Wouldn't I need to use a UNION for this kind of query? A union will combine rows (if they're compatible) from two queries, What you are asking is to assemble columns, which is entirely different. -- nathan wagner [EMAIL PROTECTED] Please don't top-post. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
Hi Adam and all, I don't get it=). How do I input the second daterange in this query? Also, I have the ID from table1. Its known in the query. Wouldn't I need to use a UNION for this kind of query? Håkan Jacobsson - System Developer RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden Mobile (+46) 736 56 97 58 Direct (+46) 8 56 24 98 05 Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22 -Ursprungligt meddelande- Från: Adam Rich [mailto:[EMAIL PROTECTED] Skickat: den 28 januari 2008 15:22 Till: Håkan Jacobsson; pgsql-general@postgresql.org Ämne: RE: [GENERAL] Getting the count(*) from two tables and two date ranges in same query > Resulting in 4 columns in the ResultSet like: > > count(*)_from_table2_between_fromdate1_and_todate1 = X > count(*)_from_table2_between_fromdate2_and_todate2 = Y > count(*)_from_table3_between_fromdate1_and_todate1 = Z > count(*)_from_table3_between_fromdate2_and_todate2 = V > > Is this possible? Select t1.id, sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2 from t1, t2, t3 where t1.id=t2.id and t2.id = t3.id group by t1.id ---(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
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
Adam Rich wrote: Resulting in 4 columns in the ResultSet like: count(*)_from_table2_between_fromdate1_and_todate1 = X count(*)_from_table2_between_fromdate2_and_todate2 = Y count(*)_from_table3_between_fromdate1_and_todate1 = Z count(*)_from_table3_between_fromdate2_and_todate2 = V Is this possible? Select t1.id, sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2 from t1, t2, t3 where t1.id=t2.id and t2.id = t3.id group by t1.id My first thought would be to use a subselect. select (select count(table1_ID) from t2 where date > fromdate1 and date < todate1)) as X, (select count(table1_ID) from t2 where date > fromdate2 and date < todate2)) as Y, (select count(table1_ID) from t3 where date > fromdate1 and date < todate1)) as Z, (select count(table1_ID) from t3 where date > fromdate2 and date < todate2)) as V ; No idea if that's the most efficient, but it is more intuitive to me. I hadn't really been aware of 'between'. from http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html it seems that "a BETWEEN x AND y is equivalent to a >= x AND a <= y" Which is wrong (though it may be required by the standard, of course). 1 is not between 1 and 2. "between" shouldn't include the endpoints. At any rate, the OP will know what he meant by "between" and can select the appropriate operators. -- nathan wagner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Getting the count(*) from two tables and two date ranges in same query
> Resulting in 4 columns in the ResultSet like: > > count(*)_from_table2_between_fromdate1_and_todate1 = X > count(*)_from_table2_between_fromdate2_and_todate2 = Y > count(*)_from_table3_between_fromdate1_and_todate1 = Z > count(*)_from_table3_between_fromdate2_and_todate2 = V > > Is this possible? Select t1.id, sum(case when t2.date between d1 and d2 then 1 else 0 end) as sum1, sum(case when t3.date between d1 and d2 then 1 else 0 end) as sum2 from t1, t2, t3 where t1.id=t2.id and t2.id = t3.id group by t1.id ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Getting the count(*) from two tables and two date ranges in same query
Hi all, I have three tables like this: table1 with column table1_ID table2 with columns table1_ID, date ..etc table3 with columns table1_ID, date ..etc I would like to create one query to retrieve the rowcount ( count(*) ) from both table2 and table3 WHERE date BETWEEN fromdate1 AND todate1 in one column in the ResultSet WHERE date BETWEEN fromdate2 AND todate2 in another column in the ResultSet WHERE table1_ID = some_value .. Resulting in 4 columns in the ResultSet like: count(*)_from_table2_between_fromdate1_and_todate1 = X count(*)_from_table2_between_fromdate2_and_todate2 = Y count(*)_from_table3_between_fromdate1_and_todate1 = Z count(*)_from_table3_between_fromdate2_and_todate2 = V Is this possible? /Best regards, Håkan Jacobsson - System developer in Sweden Håkan Jacobsson - System Developer RELEVANT TRAFFIC EUROPE AB, Riddarg 17D, SE-114 57 Sthlm, Sweden Mobile (+46) 736 56 97 58 Direct (+46) 8 56 24 98 05 Phone to office (+46) 8 678 97 50 || Fax (+46) 8 661 19 22
Re: [GENERAL] Is news.postgresql.org down?
On Mon, Jan 28, 2008 at 10:07:43AM +0100, Rainer Bauer wrote: > Hello, > > I cannot retrieve any list messages through the news server anymore > (since last Tuesday). Are there any known problems? > > The reported error is: "503 NNTP server unavailable". Yes, it's been down for quite a long time. AFAIK, Marc has a plan for fixing it, but I don't know the timeframe. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Error after upgrade
Hi Im in the process of moving an application that is currently using Postgres 7.4.8 to a new system running Postgres 8.1.9 Ive managed to use pg_dump to get all the data migrated across, however I have an update script that causes an error on the new system but works on the older one, I just cant seem to get to the bottom of it and was hoping someone could point me the right way! The script is pretty basic and uses Perl DBI to just update a record, an extract is below $domid = '288' ; $sdate = '2008-01-24'; $uinctr = '1'; $uinsize = '1000'; $uinspam = '0'; $uinvir = '0'; $uoutctr = '1'; $uoutsize = '100'; $uoutspam = '0'; $uoutvir = '0'; $uname = 'paul'; $stattype = "W"; $statmode = "U"; $uweeklysqlu = "UPDATE stats_? SET statdate=?,inctr=inctr+?,insize=insize+?,inspam=inspam+?,invir=invir+?,outct r=outctr+?,outsize=outsize+?,outspam=outspam+?,outvir=outvir+? WHERE account=? AND stattype=? AND statmode=? AND extract(week from statdate)=extract(week from date ?) and extract(year from statdate)=extract(year from date ?)"; $uweeklysqlu=$dbv->prepare($uweeklysqlu); $uweeklysqlu->execute($domid,$sdate,$uinctr,$uinsize,$uinspam,$uinvir,$uoutc tr,$uoutsize,$uoutspam,$uoutvir,$uname,$stattype,$statmode,$sdate,$sdate); The above gets the error "DBD::Pg::st execute failed: ERROR: syntax error at or near "$14" at character 277" when run against Postgres 8.1.9 but works against 7.4.8, ive tried the script but substituting the variables in the prepare instead of execute and it works i.e. $uweeklysqlu1 = "UPDATE stats_$domid SET statdate='$sdate',inctr=inctr+'$uinctr',insize=insize+'$uinsize',inspam=insp am+'$uinspam',invir=invir+'$uinvir',outctr=outctr+'$uoutctr',outsize=outsize +'$uoutsize',outspam=outspam+'$uoutspam',outvir=outvir+'$uoutvir' WHERE account='$uname' AND stattype='$stattype' AND statmode='$statmode' AND extract(week from statdate)=extract(week from date '$sdate') and extract(year from statdate)=extract(year from date '$sdate')"; $uweeklysqlu=$dbv->prepare($uweeklysqlu); $uweeklysqlu->execute; I think its something very simple but I just cant seem to solve it? Any ideals Kind Regards Paul ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Very long execution time of "select nextval('..');"
Hi Greg, first fo all: thanks a lot. i think i understood most of your comments, but - of course - have now more questions than before :-) Am Montag, 28. Januar 2008 01:07 schrieb Greg Smith: > On Sun, 27 Jan 2008, [EMAIL PROTECTED] wrote: > > ok, at the moment i got some traffic and my load is at 1.5. But now with > > logging the timestamp I have seen that the long durations are quite > > regular at intervals of 10 minutes. > > Sure sounds like checkpoints. You should turn on the checkpoint warning > feature so it always triggers and see if the long queries completely just > after the checkpoints finish. i read und tried to understand all about checkpoints. i have set checkpoint_warning=3600 and restartet Postgresql but i dont see any checkpoint warnings inside my logs with log_min_messages = info log_min_error_statement = warning so i think a checkpoint warning should pop up frequently as i have checkpoint_segments = 10 checkpoint_timeout = 300 so at least each five minutes a warning should be shown, but it isn't. > Notes on that and what you can do to > possibly improve checkpoint behavior are at > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm The > early parts of that mostly refer to 8.2 but 8.1 is basically the same in > this area. so this what i have done to tune checkpoints. Please let me know if it sounds reasonable. It list all parameters which in my opinion affect write performance and which dont have their default value fsync = on wal_buffers = 16 bgwriter_delay = 200 bgwriter_lru_percent = 10.0 bgwriter_lru_maxpages = 100 bgwriter_all_percent = 5 bgwriter_all_maxpages = 200 checkpoint_timeout = 300 checkpoint_segments = 10 checkpoint_warning = 3600 shared_buffers = 3 work_mem = 10240 maintenance_work_mem = 163840 max_fsm_pages = 50 effective_cache_size = 18 random_page_cost = 3 autovacuum = on autovacuum_naptime = 3000 stats_start_collector = on stats_command_string = off stats_block_level = on stats_row_level = on stats_reset_on_server_start = on but after a restart i still got lots of queries above 500ms (my new value for log_min_duration) and still some of them are so simple as select nextval. (Logs are shortend for your convinience): 11:53:48 duration: 14473.594 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 12527.846 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 12488.325 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 10300.745 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 4638.686 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 4012.015 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 2087.131 ms EXECUTE [PREPARE: select nextval 11:53:48 duration: 11669.099 ms EXECUTE [PREPARE: select nextval 11:54:14 duration: 2007.563 ms EXECUTE [PREPARE: select nextval > > but what in hell can make nextval take so long? even if checkpointing is > > badly configured. > > You're in a situation where your amount of RAM far exceeds your disk I/O > capabilities. Brutally bad checkpoints are easy to encounter in that > setup. Linux by default will use 10% of RAM to hold writes. At > checkpoint time, that entire Linux buffer cache has to be cleared of > database writes on top of what's written by the checkpoint itself. How > long do you think it takes to write >800MB of database data with a > significant random-access component to it when your disk is a simple > RAID-1? 20 seconds is not out of the question. ok. But is it something you really recommend? I fear changing such values as i do not understand their impact. > You may want to significantly reduce the size of the Linux write buffer > and see if that helps. > http://www.westnet.com/~gsmith/content/linux-pdflush.htm goes over theory > and suggestions here. > > > I always thought that nextval is one of the fastest operations. > > So if it takes 500 ms, fine. things like this can always happen, but 20 > > seconds sounds more like a hardware failure. But i can't see any. > > Just about everything gets blocked behind the worse checkpoint spikes. > The thing that kind of bothers me about your case is that I'd expect other > queries would also be blocked and you'd have a whole set of >250ms ones > lined up just after the checkpoint is done. That you're only reporting > issues with nextval makes me wonder if there isn't some other locking > driving the main behavior, perhaps something that just gets worse at > checkpoint time rather than being directly caused by it. i have many queries far above 250ms. I just choosed to report nextval statements only because other statements are rather complicated and i would just get an answer about "explain analyze" which is not my problem. I appreciate your help very much. thanks a lot. kind regards, janning ---(end of broadcast)--- TIP 9: In vers
Re: [GENERAL] A select DISTINCT query? - followup Q
In article <[EMAIL PROTECTED]>, Phil Rhoades <[EMAIL PROTECTED]> writes: > People, >> select count(*) as cnt, name from tst group by name having count(*) = 1 > This worked for my basic example but not for my actual problem - I get > "column comment must appear in the GROUP BY clause or be used in an > aggregate function" errors so I have a related question: > With table: > name comment > 1first comment > 2second comment > 3third comment > 3fourth comment > 4fifth comment > 5sixth comment > - how can I use something like the previous select statement but where > the comment field does not appear in the "group by" clause and gives the > following result: > 1first comment > 2second comment > 4fifth comment > 5sixth comment If you want to select both columns, but have uniqueness over the first only, you can use a derived table: SELECT tbl.name, tbl.comment FROM tbl JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t ON t.name = tbl.name ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Is news.postgresql.org down?
Hello, I cannot retrieve any list messages through the news server anymore (since last Tuesday). Are there any known problems? The reported error is: "503 NNTP server unavailable". Rainer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
