Re: [GENERAL] Upgrading side by side in Gentoo
Erik Jones wrote: P.S. To whomever said that Gentoo for for single users running cutting edge software, poppycock. That was me. Andrus said in a former post on this thread: >> I have ... no experiences on Linux. I stand by my assertion that his company should not be running Gentoo in a production environment. I have quite a bit of experience in Gentoo and other distros, and for Andrus's situation, I continue to recommend a binary distro with committed multi-year support. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seek within Large Object, within PL/* function?
Adam Seering wrote: Hi, I'm new here, apologies if this is the wrong list... I'm playing around with storing custom preprocessed data structures within Large Objects. I'd like to be able to write a custom function that will, within a query, let me select out particular bytestrings from the middle of a Large Object (within C, I think 'lo_lseek' and 'lo_read' would get me what I want). I'd like to do so from some PL/* embedded language, rather than a compiled C module. I can't find any documentation on doing this, though. Is it possible? Thoughts? According to http://www.postgresql.org/docs/8.3/static/lo-funcs.html , the functions are server side too postgres=# select proname from pg_proc where proname like 'lo%'; proname - lo_close lo_creat lo_create lo_export lo_import lo_lseek lo_open lo_tell lo_truncate lo_unlink log log log loread lower lowrite It's odd that loread and lowrite don't follow the naming convention with the underscore (8.3.3 win32)? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] backup and permissions
Hello Scott, thanks for your answer. I've just noticed that my first message lacked some important info. First, this is an accounting software, and there's only one database. Almost all of the options (buttons, generally ) are stored in a set of tables, beside the database privileges needed to work properly. Permissions are assigned from the application, and they're translated internally as a list of grant/revoke commands on tables, sequences, functions and schemas. Every application user is a pgsql role with login and nosuperuser options. Right now there are about 20 users, 3 of them with admin permissions (still regular users, but they can execute functions and modify data that others can't). They can't create, alter or drop database objects. Doing backups will be just an option more to enable/disable and it's not likely to be a public one, just a few people will be allowed to do it. What they do with the backup file is beyond my scope, of course, but I wouldn't like to see a bunch of users having fun with the database server ;) . This is why I'm thinking of a temporary superuser privilege, or even a temporary read access to let a user execute pg_dump and pg_dumpall without being a superuser. By the way, I don't like the idea of backing up the postgres account, I might need to create a customized dump to include just the regular roles and their md5-passwords. Maybe, as said by a scottish girl: I think I'm paranoid... Cheers.
[GENERAL] Seek within Large Object, within PL/* function?
Hi, I'm new here, apologies if this is the wrong list... I'm playing around with storing custom preprocessed data structures within Large Objects. I'd like to be able to write a custom function that will, within a query, let me select out particular bytestrings from the middle of a Large Object (within C, I think 'lo_lseek' and 'lo_read' would get me what I want). I'd like to do so from some PL/* embedded language, rather than a compiled C module. I can't find any documentation on doing this, though. Is it possible? Thoughts? Thanks, Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tweaking PG (again)
On Fri, Nov 14, 2008 at 10:57 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Yeah, but note that the planner knows darn well that this will be an > expensive query --- 493427.14 cost units estimated to fetch 2 rows! > > My interpretation is that the condition on user_id is horribly > nonselective (at least for this value of user_id) and the planner knows > it. The condition on url_encrypted *is* selective, and the planner > knows that too, but there's nothing it can do about it --- the best > available plan is to fetch all the rows matching by user_id and then > filter them on url_encrypted. > > Consider creating an index on url_encrypted if you need this type of > query to go fast. Thanks Tom. Yes, I have considered indexing url_encrypted too. That would be a very large index though, space-wise, but may increase the speed. You are right that only "user_id" is not too selective. The two together (user_id, url_encrypted) should be unique in my case. So I can now think of making a unique index with these two fields. Questions: 1. If I have a unique index on (user_id, url_encrypted), then will queries asking only for user_id also use this index? Or should i simply have separate indexes on user_id and url_encrypted? I vaguely recall reading somewhere that compound indexes may have been useful in MySQL but according to PG's more advanced planner having two separate indexes on the columns works better. 2. Is there a production equivalent of REINDEX? Last time I tried CREATE INDEX CONCURRENTLY overnight, by the morning it had croaked with these errors: --- ERROR: deadlock detected DETAIL: Process 6663 waits for ShareLock on transaction 999189656; blocked by process 31768. Process 31768 waits for ShareUpdateExclusiveLock on relation 50002 of database 41249; blocked by process 6663 --- Naturally, when I see the table now, this attempted index is marked "INVALID". The manual says I should either drop it and recreate it, or REINDEX it again. But this is a production database on a semi-busy website and cannot take time off. What is my recourse for a kind of live REINDEX? Can I create a new index without locking the database? 3. Basically, design wise, I use url_encrypted to check if a user_id already has a url associated with him. This kind of a unique constraint check (user_id, url_encrypted). Used only when INSERTing a new record -- if the user has it already, then simply update values if needed and return the current row. Otherwise, INSERT new row. I do this check+update+insert with three SQLs. Is there one way of doing it in SQL in PG? Many thanks for all the help thus far! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] backup and permissions
On Thu, Nov 13, 2008 at 5:30 PM, Fernando Moreno <[EMAIL PROTECTED]> wrote: > Hi, I'm working on a little backup utility for a desktop application. It's > going to execute pg_dumpall (-r) and pg_dump, but first I have to deal with > the permissions needed to do that: > > 1. Users (pgsql roles) enabled to backup would be superusers all the time. > This sounds insecure. So, letting a user have all your data, but no power over the database is somehow more secure? I kinda get your point but wouldn't go so far as to call it insecure to require a superuser to do backups. Plus, any user who owns a db can back it up. So, you can always have individual user accounts backup individual databases. Keep in mind pg_dumpall backs up things like user accounts as well. You don't want tom dick and harry backing up user accounts do you? > 2. Users will get superuser access through a security definer function just > before the backup, then they'll be nosuperuser again. An interrupted backup > process would be dangerous, but I could check whether or not this clause is > enabled, every time a user connects. Still risky. Sounds like a lot of work to avoid having users just back up individual databases they have permissions on. > 3. Users will just be able to read every object in the database, and > pg_authid. I've done some tests and this seems enough. > > I need some advice to choose the better/safer option, what would you do? Backup with a superuser. Or split the backups to users who own their own databases. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to "group" several records with same timestamp into one line?
Hi Ho! --- On Thu, 11/13/08, Brent Wood <[EMAIL PROTECTED]> wrote: > You need to use a self relation, not a group by, as no data > are being aggregated into a new single value, which is what > the group by achieves. > > This joins a table to itself, so that columns in it can be > replicated. The key is that the where clause in each case > needs to just select one channel, so it acts like a similar > table but contains different rows. > > Because you used a char() instead of varchar() for channel, > you may find your string 'channel1' has spaces in it > to pad it to the specified length, in which case the where > clauses below can use "like '%channel1%'" > instead of "= 'channel1'" > or you can strip the spaces before the comparison, eg: > where "trim(a.channel)='channel1'". I hope > this makes sense. > > eg: select a.data, > a.unit, > b.data, > b.unit, > c.data, > c.unit, > d.data, > d.unit, > a.create_on >from record data a, > record-data b, > record_data c, > record_data d >where a.channel='channel1' >and b.channel='channel2' >and c.channel='channel3' >and d.channel=channel4 >and b.create_on = a.create_on >and c.create_on = a.create_on >and d.create_on = a.create on; > > Thus table a comprises all records from channel1, etc... > and they are joined on a common timestamp. > > NOTE: if any of the channels are null for any given > timestamp, you will get no record for that timestamp using > this syntax, even if other channels had data, because the > query uses an inner join. If this is a problem then > you'll need to reword the query as an outer join. Isn't that something like this is better handled at the application level instead of the DB level? IOW, isn't that the cost of doing the query above far more expensive than doing a little coding at the application level? May I know your opinion? Thanks. > HTH, > >Brent Wood > > > Brent Wood > DBA/GIS consultant > NIWA, Wellington > New Zealand Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tweaking PG (again)
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Thu, Nov 13, 2008 at 2:04 PM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: >> explain analyze SELECT alias, id, title, private_key, aliasEntered >> FROM books >> WHERE user_id = 'MYUSER' AND url_encrypted = >> 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4' ; >> >> QUERY PLAN >> >> Index Scan using new_idx_books_userid on books (cost=0.00..493427.14 >> rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1) >> Index Cond: ((user_id)::text = 'MYUSER'::text) >> Filter: (url_encrypted = 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar) >> Total runtime: 8400.349 ms >> (4 rows) > 8.4 seconds is a very long time to spend looking up a single record. Yeah, but note that the planner knows darn well that this will be an expensive query --- 493427.14 cost units estimated to fetch 2 rows! My interpretation is that the condition on user_id is horribly nonselective (at least for this value of user_id) and the planner knows it. The condition on url_encrypted *is* selective, and the planner knows that too, but there's nothing it can do about it --- the best available plan is to fetch all the rows matching by user_id and then filter them on url_encrypted. Consider creating an index on url_encrypted if you need this type of query to go fast. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum output question
"Dan Armbrust" <[EMAIL PROTECTED]> writes: > Why did those particular tables and indexes take _so_ long to vacuum? > Perhaps we have a disk level IO problem on this system? FWIW, I agree with Scott that you seem to have an overstressed I/O system. It's hard to tell why from here. > Can someone tell me what 'CPU 44.46s/11.82u sec' means? I have a > guess, but I'm not sure. That's the vacuum process's system and user CPU-time consumption as reported by getrusage(2). It's evidently only a minor component of the elapsed runtime, though, so you need to be looking at I/O costs. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] backup and permissions
Hi, I'm working on a little backup utility for a desktop application. It's going to execute pg_dumpall (-r) and pg_dump, but first I have to deal with the permissions needed to do that: 1. Users (pgsql roles) enabled to backup would be superusers all the time. This sounds insecure. 2. Users will get superuser access through a security definer function just before the backup, then they'll be nosuperuser again. An interrupted backup process would be dangerous, but I could check whether or not this clause is enabled, every time a user connects. Still risky. 3. Users will just be able to read every object in the database, and pg_authid. I've done some tests and this seems enough. I need some advice to choose the better/safer option, what would you do? Thanks in advance.
Re: [GENERAL] vacuum output question
On Thu, Nov 13, 2008 at 4:08 PM, Dan Armbrust <[EMAIL PROTECTED]> wrote: > I have a system backed by a PostgreSQL DB at a customer site that > mysteriously slowed way down - and couldn't keep up with the load for > no apparent reason. > > I had them run a vacuum analyze verbose on my database, and had these > lines come back which made me suspicious: > > INFO: index "ix_cpe_ispid" now contains 41626 row versions in 13727 pages > DETAIL: 5224 index row versions were removed. > 1543 index pages have been deleted, 1373 are currently reusable. > CPU 13.09s/3.51u sec elapsed 157.85 sec. > > INFO: index "ix_cpe_enable" now contains 41628 row versions in 29417 pages > DETAIL: 5224 index row versions were removed. > 3706 index pages have been deleted, 3291 are currently reusable. > CPU 31.27s/8.22u sec elapsed 687.60 sec. > > INFO: "cpe": found 5224 removable, 41626 nonremovable row versions in > 1303 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 22416 unused item pointers. > 0 pages are entirely empty. > CPU 44.46s/11.82u sec elapsed 852.85 sec. That's a fair bit of dead space, but 60k rows isn't really that many. > Why did those particular tables and indexes take _so_ long to vacuum? > Perhaps we have a disk level IO problem on this system? Assuming pagesize is 8k, then we're talking about scanning 1303*8192 bytes or 10 Megabytes. My laptop can scan that in less than a second. So, either the hard drive is incredibly fragmented, or there's something wrong with that machine. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Granting read-only access to an existing database?
What's the simplest way to grant read-only access to an existing database? One approach I guess would be to create a user who has SELECT but not INSERT etc privileges. But it appears that GRANT SELECT does not work at the schema or database level. This means I'd not only have to create hundreds of GRANT statements, but also remember to issue an additional GRANT whenever a new table is created! I came across some PL/pgSQL procedures for doing batch GRANTs, but they appear to be outdated (i.e. don't work with 8.3) and are "run at your own risk". There was also mention that pgAdmin had a function to batch GRANTs, but I couldn't find any such thing in the current version... Any other ideas? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Tweaking PG (again)
Phoenix Kiula escribió: > >> > >> Index Scan using new_idx_books_userid on books (cost=0.00..493427.14 > >> rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1) > >> Index Cond: ((user_id)::text = 'MYUSER'::text) > >> Filter: (url_encrypted = > >> 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar) > >> Total runtime: 8400.349 ms > >> (4 rows) > > > > 8.4 seconds is a very long time to spend looking up a single record. > > Is this table bloated? What does > > > > vacuum verbose books; > Thanks but this table "books" has autovac on, and it's manually > vacuumed every hour! Perhaps try reindexing it. What kind of index is new_idx_books_userid anyway? btree? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum output question
I have a system backed by a PostgreSQL DB at a customer site that mysteriously slowed way down - and couldn't keep up with the load for no apparent reason. I had them run a vacuum analyze verbose on my database, and had these lines come back which made me suspicious: INFO: index "ix_cpe_ispid" now contains 41626 row versions in 13727 pages DETAIL: 5224 index row versions were removed. 1543 index pages have been deleted, 1373 are currently reusable. CPU 13.09s/3.51u sec elapsed 157.85 sec. INFO: index "ix_cpe_enable" now contains 41628 row versions in 29417 pages DETAIL: 5224 index row versions were removed. 3706 index pages have been deleted, 3291 are currently reusable. CPU 31.27s/8.22u sec elapsed 687.60 sec. INFO: "cpe": found 5224 removable, 41626 nonremovable row versions in 1303 pages DETAIL: 0 dead row versions cannot be removed yet. There were 22416 unused item pointers. 0 pages are entirely empty. CPU 44.46s/11.82u sec elapsed 852.85 sec. Why did those particular tables and indexes take _so_ long to vacuum? Perhaps we have a disk level IO problem on this system? Can someone tell me what 'CPU 44.46s/11.82u sec' means? I have a guess, but I'm not sure. Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] error on vacuum - could not read block
On Thursday 13 November 2008, "glok_twen" <[EMAIL PROTECTED]> wrote: > INFO: vacuuming "public.monthly_res_01" > > ERROR: could not read block 43775860 of relation 1663/11511/24873: read > only 4096 of 8192 bytes > > ERROR: could not read block 43775860 of relation 1663/11511/24873: read > only 4096 of 8192 bytes > > do you know the process i should follow to diagnose and repair? You have an underlying hardware issue that needs to be repaired. Then you'll probably need to recover at least this table from a backup. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Tweaking PG (again)
Thanks Scott. Responses below. >> >> (1) The culprit SELECT sql is (note that "MYUSER" in this example can >> be an IP address) -- > > So, it can be, but might not be? Darn, If it was always an ip I'd > suggest changing types. > Yes, it can either be a registered USER ID or an IP address. I thought of having two separate fields, where one is null or the other, and then indexing the concatenation of those two which I could use for the SQL. But it's difficult to revamp whole code. Instead of that, I have "user_known". If user_known is 1, then it's a user_id, otherwise it's an IP address. This is quicker than regexping for IP pattern everytime. >> explain analyze SELECT alias, id, title, private_key, aliasEntered >> FROM books >> WHERE user_id = 'MYUSER' AND url_encrypted = >> 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4' ; >> >>QUERY PLAN >> >> Index Scan using new_idx_books_userid on books (cost=0.00..493427.14 >> rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1) >> Index Cond: ((user_id)::text = 'MYUSER'::text) >> Filter: (url_encrypted = >> 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar) >> Total runtime: 8400.349 ms >> (4 rows) > > 8.4 seconds is a very long time to spend looking up a single record. > Is this table bloated? What does > > vacuum verbose books; > > say about it? Look for a line like this: > > There were 243 unused item pointers Thanks but this table "books" has autovac on, and it's manually vacuumed every hour! >> (2) The culprit INSERT sql is as follows >> >> explain analyze >> INSERT INTO books (id, book_id, url, user_known, user_id, >> url_encrypted, alias, title, private_key, status, modify_date) >>values >>( >> 9107579 >> ,'5f7gb' >> ,'http://www.google.com' >> ,'0' >> ,'MYUSER' >> ,'73684da5ef05d9589f95d8ba9e4429ea062549c7' >> ,'5f7gb' >> ,'' >> ,'' >> ,'Y' >> ,now() >>) >> ; >> >> QUERY PLAN >> >> Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.017..0.022 >> rows=1 loops=1) >> Total runtime: 106.747 ms >> (2 rows) >> >> Time: 3421.424 ms > > When the total run time measured by explain analyze is much lower than > the actual run time, this is usually either a trigger firing / fk > issue, or you've got a really expensive (cpu wise) time function on > your OS. Since there's only one loop here, I'm gonna guess that > you've got some FK stuff going on. Got a related fk/pk field in > another table that needs an index? I thought that 8.3 gave some info > on that stuff in explain analyze, but I'm not really sure. Yes there is a table VISITCOUNT that has a foreign key on books(id). But why should that be invoked? Shouldn't that fk be called into question only when a row is being inserted/updated in VISITCOUNT table and not BOOKS? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] error on vacuum - could not read block
i have a big table - about 450GB each of data and index use. i keep getting a hint saying the database needs a vacuum: WARNING: database "postgres" must be vacuumed within 10970738 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". however when i run the vacuum i always get this error when it vacuums the big table: INFO: vacuuming "public.monthly_res_01" ERROR: could not read block 43775860 of relation 1663/11511/24873: read only 4096 of 8192 bytes ERROR: could not read block 43775860 of relation 1663/11511/24873: read only 4096 of 8192 bytes do you know the process i should follow to diagnose and repair? -- View this message in context: http://www.nabble.com/error-on-vacuum---could-not-read-block-tp20490557p20490557.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 1:09 PM, David Wilson <[EMAIL PROTECTED]> wrote: > On Thu, Nov 13, 2008 at 2:03 PM, <[EMAIL PROTECTED]> wrote: >> I have several tables that when I run VACUUM FULL on, they are under 200k, >> but after a day of records getting added they grow to 10 to 20 megabytes. >> They get new inserts and a small number of deletes and updates. >> >> seq_scan | 32325 >> seq_tup_read | 39428832 >> idx_scan | 6590219 >> idx_tup_fetch| 7299318 >> n_tup_ins| 2879 >> n_tup_upd| 6829984 >> n_tup_del| 39 >> n_tup_hot_upd| 420634 >> n_live_tup | 2815 >> n_dead_tup | 0 > > Can you define "small number of deletes and updates"? The stats above > would disagree with "small". Remember that every update creates a new, > updated version of the row, which is where the increase is coming > from. And don't forget to look into failed inserts. Those too create dead tuples. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tweaking PG (again)
On Thu, Nov 13, 2008 at 2:04 PM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > On Fri, Nov 14, 2008 at 3:10 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: >> On Thu, Nov 13, 2008 at 11:59 AM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: >>> Hi. >>> >>> I had tweaked my PG 8.2.6 with the very kind help of this list a >>> couple years ago. It has been working fine, until recently. Not sure >>> if it is after the update to 8.3 or because my DB has been growing, >>> but the db is very slow now and the cache doesn't seem enough. >> >> Everything you posted looks pretty normal. I'd find the slowest >> queries and post explain analyze to see what's happening. > > My logs are full of > > (1) One SELECT sql > (2) And INSERT and UPDATE sql to my main table, called "books" > > The definition of "books" is as follows -- > > > >Table "public.books" >Column |Type | > Modifiers > ---+-+-- > id| bigint | not null > book_id | character varying(10) | not null > alias | character varying(20) | not null > url | text| not null > user_known| smallint| not null default 0 > user_id | character varying(45) | not null > url_encrypted | character(40) | default ''::bpchar > title | character varying(500) | > status| character(1)| default 'Y'::bpchar > modify_date | timestamp without time zone | > Indexes: >"books2_pkey" PRIMARY KEY, btree (id) >"books2_alias_key" UNIQUE, btree (alias) WITH (fillfactor=75) >"new_idx_books_userid" btree (user_id) WITH (fillfactor=70) >"new_idx_modify_date" btree (modify_date) WITH (fillfactor=75) >"new_idx_userknown" btree (user_id) WITH (fillfactor=70) WHERE > user_known = 1 > Check constraints: >"books2_id_check" CHECK (id > 0) >"books2_url_check" CHECK (url <> ''::text) >"books2_user_id_check" CHECK (user_id::text <> ''::text) >"books_alias_check" CHECK (alias::text ~ '[-~a-z0-9_]'::text) > > > > > (1) The culprit SELECT sql is (note that "MYUSER" in this example can > be an IP address) -- So, it can be, but might not be? Darn, If it was always an ip I'd suggest changing types. > explain analyze SELECT alias, id, title, private_key, aliasEntered > FROM books > WHERE user_id = 'MYUSER' AND url_encrypted = > 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4' ; > >QUERY PLAN > > Index Scan using new_idx_books_userid on books (cost=0.00..493427.14 > rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1) > Index Cond: ((user_id)::text = 'MYUSER'::text) > Filter: (url_encrypted = 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar) > Total runtime: 8400.349 ms > (4 rows) 8.4 seconds is a very long time to spend looking up a single record. Is this table bloated? What does vacuum verbose books; say about it? Look for a line like this: There were 243 unused item pointers > (2) The culprit INSERT sql is as follows > > explain analyze > INSERT INTO books (id, book_id, url, user_known, user_id, > url_encrypted, alias, title, private_key, status, modify_date) >values >( > 9107579 > ,'5f7gb' > ,'http://www.google.com' > ,'0' > ,'MYUSER' > ,'73684da5ef05d9589f95d8ba9e4429ea062549c7' > ,'5f7gb' > ,'' > ,'' > ,'Y' > ,now() >) > ; > > QUERY PLAN > > Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.017..0.022 > rows=1 loops=1) > Total runtime: 106.747 ms > (2 rows) > > Time: 3421.424 ms When the total run time measured by explain analyze is much lower than the actual run time, this is usually either a trigger firing / fk issue, or you've got a really expensive (cpu wise) time function on your OS. Since there's only one loop here, I'm gonna guess that you've got some FK stuff going on. Got a related fk/pk field in another table that needs an index? I thought that 8.3 gave some info on that stuff in explain analyze, but I'm not really sure. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tweaking PG (again)
On Fri, Nov 14, 2008 at 3:10 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Thu, Nov 13, 2008 at 11:59 AM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: >> Hi. >> >> I had tweaked my PG 8.2.6 with the very kind help of this list a >> couple years ago. It has been working fine, until recently. Not sure >> if it is after the update to 8.3 or because my DB has been growing, >> but the db is very slow now and the cache doesn't seem enough. > > Everything you posted looks pretty normal. I'd find the slowest > queries and post explain analyze to see what's happening. Thanks Scott. That is a relief. My logs are full of (1) One SELECT sql (2) And INSERT and UPDATE sql to my main table, called "books" The definition of "books" is as follows -- Table "public.books" Column |Type | Modifiers ---+-+-- id| bigint | not null book_id | character varying(10) | not null alias | character varying(20) | not null url | text| not null user_known| smallint| not null default 0 user_id | character varying(45) | not null url_encrypted | character(40) | default ''::bpchar title | character varying(500) | status| character(1)| default 'Y'::bpchar modify_date | timestamp without time zone | Indexes: "books2_pkey" PRIMARY KEY, btree (id) "books2_alias_key" UNIQUE, btree (alias) WITH (fillfactor=75) "new_idx_books_userid" btree (user_id) WITH (fillfactor=70) "new_idx_modify_date" btree (modify_date) WITH (fillfactor=75) "new_idx_userknown" btree (user_id) WITH (fillfactor=70) WHERE user_known = 1 Check constraints: "books2_id_check" CHECK (id > 0) "books2_url_check" CHECK (url <> ''::text) "books2_user_id_check" CHECK (user_id::text <> ''::text) "books_alias_check" CHECK (alias::text ~ '[-~a-z0-9_]'::text) (1) The culprit SELECT sql is (note that "MYUSER" in this example can be an IP address) -- explain analyze SELECT alias, id, title, private_key, aliasEntered FROM books WHERE user_id = 'MYUSER' AND url_encrypted = 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4' ; QUERY PLAN Index Scan using new_idx_books_userid on books (cost=0.00..493427.14 rows=2 width=31) (actual time=0.428..8400.299 rows=1 loops=1) Index Cond: ((user_id)::text = 'MYUSER'::text) Filter: (url_encrypted = 'bed59c2f2d92e306d4481a276bd8a78c4b0532d4'::bpchar) Total runtime: 8400.349 ms (4 rows) (2) The culprit INSERT sql is as follows explain analyze INSERT INTO books (id, book_id, url, user_known, user_id, url_encrypted, alias, title, private_key, status, modify_date) values ( 9107579 ,'5f7gb' ,'http://www.google.com' ,'0' ,'MYUSER' ,'73684da5ef05d9589f95d8ba9e4429ea062549c7' ,'5f7gb' ,'' ,'' ,'Y' ,now() ) ; QUERY PLAN Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.017..0.022 rows=1 loops=1) Total runtime: 106.747 ms (2 rows) Time: 3421.424 ms -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Archive files growth!!!
How the best way to controling fast growth in my Database. atually my postgresql.conf have this: # - Checkpoints - checkpoint_segments = 15# in logfile segments, min 1, 16MB each checkpoint_timeout = 5min # range 30s-1h #checkpoint_warning = 30s # 0 is off # - Archiving - archive_command = 'path' # command to use to archive a logfile segment #archive_timeout = 0# force a logfile segment switch after this # many seconds; 0 is off thnks Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com. http://br.new.mail.yahoo.com/addresses
Re: [GENERAL] sort_mem param of postgresql.conf
On Thu, Nov 13, 2008 at 7:42 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > > Other alternatives worth reading about: >RESET work_mem >SET LOCAL work_mem > nice , thanks :) -- GJ
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 2:03 PM, <[EMAIL PROTECTED]> wrote: > I have several tables that when I run VACUUM FULL on, they are under 200k, > but after a day of records getting added they grow to 10 to 20 megabytes. > They get new inserts and a small number of deletes and updates. > > seq_scan | 32325 > seq_tup_read | 39428832 > idx_scan | 6590219 > idx_tup_fetch| 7299318 > n_tup_ins| 2879 > n_tup_upd| 6829984 > n_tup_del| 39 > n_tup_hot_upd| 420634 > n_live_tup | 2815 > n_dead_tup | 0 Can you define "small number of deletes and updates"? The stats above would disagree with "small". Remember that every update creates a new, updated version of the row, which is where the increase is coming from. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sort_mem param of postgresql.conf
Sam Mason <[EMAIL PROTECTED]> writes: > On Thu, Nov 13, 2008 at 02:59:34PM +, Grzegorz Jaaakiewicz wrote: >> so how do I change it back to default (without knowing what the previous val >> was). I suppose having it in a transaction won't do :P > If by "default" you mean whatever was in the config file, you can do: > set work_mem = default; > A transaction followed by ROLLBACK appears to work as well. Other alternatives worth reading about: RESET work_mem SET LOCAL work_mem regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
Ok, I work at a hosting company that runs Gentoo as it's main host operating system so here's the skivvy on the current status of PostgreSQL under portage. Up until just a few months ago the needed packages were called libpq and postgresql (go figure). These were *not* slotted and so did not support having multiple versions of Postgres installed from portage simultaneously. For 8.2, 8.2, and 8.3 those packages go up to 8.1.11, 8.2.7 and 8.3.1, respectively. So, recently the Postgres package maintainer decided to remedy that situation by changing the ebuild format to a slotted format to allow (supposedly) multiple versions to be installed side-by-side, these are called postgresql-base and postgresql-server and start with 8.1.11, 8.2.10, and 8.3.4. Of course, when this change was made this created much havoc in our install scripts here at work as the two package formats are incompatible (meaning you can't both libpq/postgresql and postgresql-base/postgresql-server installed at the same time) and they even changed the names of the init scripts and /etc/conf.d/ files. In addition, the first time I had the, um, opportunity to install the slotted versions of 8.2 and 8.3 side by side it didn't work out too well. They both installed fine but I ran into problems when I needed to emerge postgis linked against 8.2: the packages install wrapper scripts for the client programs that find the latest available version on the system, including pg_config. In the end I had to completely unmerge 8.3 in order to get postgis to link against 8.2. For simple upgrades this kind of thing won't be an issue. So, Andrus, if you want to upgrade to the latest version of 8.3 using portage you're going to have to unmerge 8.1 (both libpq and postgreseql) in addition to all of the steps you've already listed which are pretty standard. Given that, if something goes wrong you'll need to include umerging 8.3 (postgresql-base and postgresql-server) and re-emerging 8.1. P.S. To whomever said that Gentoo for for single users running cutting edge software, poppycock. Any self-respecting company running Gentoo should be maintaining their own portage build servers just as a Debian based company would maintain their own build servers for apt or RedHat/CentOS for rpm/yum. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
That is the expected behavior. Postgres doesn't give back disk like Java doesn't give back memory. It keeps a map of where the free space is so it can use it again. It does all this so it doesn't have to lock the table to compact it when VACUUMing. VACUUM FULL does lock the table to compact it. In practice, if you keep your free space map large enough and you have enough rows, your tables settle down to a size close to what you'd expect. I hope that helps, --Nik On Thu, Nov 13, 2008 at 2:03 PM, <[EMAIL PROTECTED]> wrote: > I am somewhat new to Postgresql and am trying to figure out if I have a > problem here. > > I have several tables that when I run VACUUM FULL on, they are under 200k, > but after a day of records getting added they grow to 10 to 20 megabytes. > They get new inserts and a small number of deletes and updates. > > A normal VACUUM does not shrink the table size, but FULL does, or dumping > and restoring the database to a test server. > > I know that some extra space is useful so disk blocks don't need to be > allocated for every insert, but this seems excessive. > > My question is... should I be worrying about this or is this expected > behaviour? I can run a daily VACUUM but if this is indicating a > configuration problem I'd like to know. > > Here is an example table. The disk size is reported at 14,049,280 bytes. > > pg_stat_user_tables for the live db... table size is 14,049,280 bytes. > > seq_scan | 32325 > seq_tup_read | 39428832 > idx_scan | 6590219 > idx_tup_fetch| 7299318 > n_tup_ins| 2879 > n_tup_upd| 6829984 > n_tup_del| 39 > n_tup_hot_upd| 420634 > n_live_tup | 2815 > n_dead_tup | 0 > > And after it is dumped and restored... size is now 188,416 bytes. > > seq_scan | 8 > seq_tup_read | 22520 > idx_scan | 0 > idx_tup_fetch| 0 > n_tup_ins| 2815 > n_tup_upd| 0 > n_tup_del| 0 > n_tup_hot_upd| 0 > n_live_tup | 2815 > n_dead_tup | 0 > > I checked for outstanding transactions and there are none. > > Thanks! > > -- > Ian Smith > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 02:03:22PM -0500, [EMAIL PROTECTED] wrote: > I have several tables that when I run VACUUM FULL on, they are under 200k, > but after a day of records getting added they grow to 10 to 20 megabytes. > They get new inserts and a small number of deletes and updates. > > A normal VACUUM does not shrink the table size, but FULL does, or dumping > and restoring the database to a test server. I'd not expect to use a FULL vacuum as part of routine maintaince. Normally, tables like this will grow until they reach some steady state and then stay there. 14MB seems a bit big for something that you'd expect to fit in 200KB though. Autovacuum is enabled by default in 8.3, but has it been disabled for some reason here? A useful thing to post would be the output of a VACUUM VERBOSE on this table when it's grown for a day. It may give some clue as to what's going on. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table bloat in 8.3
I am somewhat new to Postgresql and am trying to figure out if I have a problem here. I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. A normal VACUUM does not shrink the table size, but FULL does, or dumping and restoring the database to a test server. I know that some extra space is useful so disk blocks don't need to be allocated for every insert, but this seems excessive. My question is... should I be worrying about this or is this expected behaviour? I can run a daily VACUUM but if this is indicating a configuration problem I'd like to know. Here is an example table. The disk size is reported at 14,049,280 bytes. pg_stat_user_tables for the live db... table size is 14,049,280 bytes. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 And after it is dumped and restored... size is now 188,416 bytes. seq_scan | 8 seq_tup_read | 22520 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 2815 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 2815 n_dead_tup | 0 I checked for outstanding transactions and there are none. Thanks! -- Ian Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sort_mem param of postgresql.conf
On Thu, Nov 13, 2008 at 02:59:34PM +, Grzegorz Jaaakiewicz wrote: > On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > > Generally, if it's only a report or two that > > need a lot more working memory for sorts, you can do this at the beginning > > of them instead: > > > > set work_mem='512MB'; > > so how do I change it back to default (without knowing what the previous val > was). I suppose having it in a transaction won't do :P If by "default" you mean whatever was in the config file, you can do: set work_mem = default; A transaction followed by ROLLBACK appears to work as well. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
changing it; I've applied a patch for that. I'm still not quite convinced that Ivan isn't seeing some other issue though. Thank you In the meantime, I noticed something odd while experimenting with your test case: when running with default maintenance_work_mem = 16MB, there is a slowdown of 3x or 4x for the un-ordered case, just as you say. But at maintenance_work_mem = 200MB I see very little difference. This doesn't make sense to me --- it seems like a larger workspace should result in more difference because of greater chance to dump a lot of tuples into the index at once. Do you know why that's happening? I suppose, if maintenance_work_mem is rather big then all data of index accumulates in memory and so it writes at disk at once. With that test's options size of index is equal to 40Mb. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tweaking PG (again)
On Thu, Nov 13, 2008 at 11:59 AM, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Hi. > > I had tweaked my PG 8.2.6 with the very kind help of this list a > couple years ago. It has been working fine, until recently. Not sure > if it is after the update to 8.3 or because my DB has been growing, > but the db is very slow now and the cache doesn't seem enough. Everything you posted looks pretty normal. I'd find the slowest queries and post explain analyze to see what's happening. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tweaking PG (again)
Hi. I had tweaked my PG 8.2.6 with the very kind help of this list a couple years ago. It has been working fine, until recently. Not sure if it is after the update to 8.3 or because my DB has been growing, but the db is very slow now and the cache doesn't seem enough. ~ > free -m total used free shared buffers cached Mem: 4051 4033 18 0 6 2576 -/+ buffers/cache: 1450 2601 Swap: 2047 43 2003 Some of the SQL queries that were super fast (indexed with LIMIT 1!) are now slow too. What is a good starting point for me apart from checking the slow SQL? Because almost every SQL is now slow. I can restart the PG server and it is fast for a little while after that but then the buffer fills up I think. It's a CentOS server, Pentium Core2Duo dual processor, 6MB RAM. Same server runs Apache (low mem consumption), MySQL (for really small web stuff, not much load) and PGSQL (huge load). I can devote a lot of memory to PG, no problem. Autovacuum is on but I also manually vacuum the big tables by crontab -- per hour. This one is not a huge DB, about 5GB right now. The tables are as such: relname| rowcnt | inserted | updated | deleted ---+-+--+-+- books | 8622136 | 1852965 | 938229 | 16304 checkout_count| 261317 | 9834 | 116664 |1225 subscribers | 10180 | 1267 | 79623 | 0 interesting |4196 | 53 | 54774 | 0 pg_statistic | 411 |0 | 43104 | 0 books_deleted | 896 |16350 | 0 | 11473 users | 62865 | 2428 |2493 | 0 pg_attribute |1844 | 1322 | 575 |1321 (8 rows) Below are my CONF settings: listen_addresses = 'localhost,*' max_connections = 300 shared_buffers = 330MB effective_cache_size = 512000 max_fsm_relations= 100 max_fsm_pages= 30 work_mem = 20MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay= 20 autovacuum_naptime = 10 stats_start_collector= on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 Any pointers or advice MUCH appreciated! THANKS. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sort_mem param of postgresql.conf
2008/11/13 Scott Marlowe <[EMAIL PROTECTED]>: > On Thu, Nov 13, 2008 at 7:59 AM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> > wrote: >> >> >> On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <[EMAIL PROTECTED]> wrote: >>> >>> Be advised that the work_mem setting (and its deprecated alias sort_mem) >>> are on a per-client basis. So if you have a bunch of people running reports >>> with that setting, you might discover your server running out of memory; >>> that's a really high setting. Generally, if it's only a report or two that >>> need a lot more working memory for sorts, you can do this at the beginning >>> of them instead: >>> >>> set work_mem='512MB'; >> >> so how do I change it back to default (without knowing what the previous val >> was). I suppose having it in a transaction won't do :P > > default is 1M, but you can safely run 8 to 16 Meg with your setup. Wait I might be confusing you with someone else. What's your machine's mem, how much is shared_buffers, and what's your max_connections? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sort_mem param of postgresql.conf
On Thu, Nov 13, 2008 at 7:59 AM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > > > On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <[EMAIL PROTECTED]> wrote: >> >> Be advised that the work_mem setting (and its deprecated alias sort_mem) >> are on a per-client basis. So if you have a bunch of people running reports >> with that setting, you might discover your server running out of memory; >> that's a really high setting. Generally, if it's only a report or two that >> need a lot more working memory for sorts, you can do this at the beginning >> of them instead: >> >> set work_mem='512MB'; > > so how do I change it back to default (without knowing what the previous val > was). I suppose having it in a transaction won't do :P default is 1M, but you can safely run 8 to 16 Meg with your setup. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple COPY FROM issue
On Thu, Nov 13, 2008 at 10:18:56AM -0500, Kevin Duffy wrote: > This worked where E: is on the database server > copy imagineoptions >from 'E:\\ORGDAT~1\\data\\xxxPositions\\20081112_Options.csv' > DELIMITERS ',' CSV ; > > > This does not work fileprint-01 is a different server. > copy imagineoptions > from > \\fileprint-01\Company\xxx\Benchmarking\xxxPositions\20081112_Options.cs > v > DELIMITERS ',' CSV ; Is this exactly what you entered? if it is, you're missing quotes and escaping. You probably want something more similar to this: COPY imagineoptions FROM E'fileprint-01\\Company\\xxx\\Benchmarking\\xxxPositions\20081112_Options.csv' WITH CSV; > So the COPY FROM command in Postgres can not handle a URL Postgres doesn't handle URL's, but that's not what you entered. At most, you entered a UNC path (I believe, it's been a *long* time since I had to deal with these under windows) and not a URL. UNC paths always used to be handled transparently by the operating system and didn't need any special handling from normal processes, e.g. a Postgres server. If you could enter the command exactly as you entered it and also include the response back from the server that may help to narrow things down a bit. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Suboptimal execution plan for simple query
On Thu, Nov 13, 2008 at 01:56:11PM +0100, Markus Wollny wrote: > Sam Mason wrote: > > You may have some luck with increasing the statistics target on the > > entry_id and last_updated columns and re-ANALYZING the table. Then > > again, the fact that it thinks it's only going to get a single row > > back when it searches for the entity_id suggests that it's all a bit > > confused! > > Thank you for that suggestion. Increasing the statistics target on > entity_id from the default 10 to 30 and re-analyzing did the trick: Even higher may be good for other entities; it thinks it's getting 103 rows back for this entity, whereas infact it only gets 3 back. Or is, on average, 103 a reasonable guess? > "Limit (cost=340.75..340.75 rows=1 width=12) (actual time=0.084..0.085 > rows=1 loops=1)" > " -> Sort (cost=340.75..341.00 rows=103 width=12) (actual > time=0.081..0.081 rows=1 loops=1)" > "Sort Key: last_updated" > "-> Index Scan using idx_image_relation_entity_id on image_relation > (cost=0.00..337.30 rows=103 width=12) (actual time=0.059..0.065 rows=3 > loops=1)" > " Index Cond: (entity_id = 69560)" > "Total runtime: 0.121 ms" A target over 100 will change the way it does the stats and may produce a better fit; try the query with a few different entities (i.e. ones where you know you've got many rows in the table, and ones where you've only got one or two) and see what numbers it comes back with. The smaller the target is, the faster the queries are planned and larger targets should allow the planner to cope with more uneven datasets. If the distribution is reasonably uniform you should be able to get away with low targets, less even distributions normally require larger targets. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery
Christian Schröder wrote: we have a PostgreSQL 8.2 database on an xfs filesystem. By mistake the first blocks of this filesystem were overwritten. An xfs_repair reconstructed the superblock and also found many orphaned files and directories. Actually, all we have on the filesystem now is in "lost+found". ;-) When I look in "lost+found" I have many files that *could* be database files, but since the original name of the files is unknown I cannot tell for sure. I have found a directory that looks like the original "data" directory, with stuff like "postmaster.log", "pg_hba.conf" and even subdirectories "base", "global" etc. in it. I have been able to start postgresql from this directory, but when I tried to access the most important database I got a message that the database directory could not be found. Indeed, this directory is missing in "base", but there is a chance that some of the other files might be the original content of this directory. Is there any way to find out which of the files is really a postgres data file? Or even for which database? Although the database file and page layout are described in the manual, I could not find an exact description of the file format, e.g. any magic numbers at the beginning of the file. Hmmm, no idea? :-( Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sort_mem param of postgresql.conf
On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > Be advised that the work_mem setting (and its deprecated alias sort_mem) > are on a per-client basis. So if you have a bunch of people running reports > with that setting, you might discover your server running out of memory; > that's a really high setting. Generally, if it's only a report or two that > need a lot more working memory for sorts, you can do this at the beginning > of them instead: > > set work_mem='512MB'; > so how do I change it back to default (without knowing what the previous val was). I suppose having it in a transaction won't do :P -- GJ
[GENERAL] pgcrypto contrib
I am trying to develop a trigger that will post a new account into a table in another db sing dblink that is part of the egroupware web app that uses tripledes as the algorithm. I can't seem to find a combination for gen_salt that produces the correct crypt password, however, my knowledge in this area is limited. Does anyone know if pgcrypto is able to produce this type of algorithm and have suggestions how I might be able to get it done? -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
Teodor Sigaev <[EMAIL PROTECTED]> writes: >> We could extend IndexBuildHeapScan's API to support that, but I'm >> not quite convinced that this is the issue. > That extension might be useful for bitmap index too to simplify index > creation > process. Maybe, but in any case the measurable GIN speed penalty justifies changing it; I've applied a patch for that. I'm still not quite convinced that Ivan isn't seeing some other issue though. In the meantime, I noticed something odd while experimenting with your test case: when running with default maintenance_work_mem = 16MB, there is a slowdown of 3x or 4x for the un-ordered case, just as you say. But at maintenance_work_mem = 200MB I see very little difference. This doesn't make sense to me --- it seems like a larger workspace should result in more difference because of greater chance to dump a lot of tuples into the index at once. Do you know why that's happening? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple COPY FROM issue
This worked where E: is on the database server copy imagineoptions from 'E:\\ORGDAT~1\\data\\xxxPositions\\20081112_Options.csv' DELIMITERS ',' CSV ; This does not work fileprint-01 is a different server. copy imagineoptions from \\fileprint-01\Company\xxx\Benchmarking\xxxPositions\20081112_Options.cs v DELIMITERS ',' CSV ; So the COPY FROM command in Postgres can not handle a URL Thanks for your attention to this matter. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of brian Sent: Wednesday, November 12, 2008 5:38 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] simple COPY FROM issue The file must be on the server. IIRC, with COPY FROM (as opposed to COPY TO) the path can be relative to the server process dir but it's probably a good idea to always use an absolute path. If you wish to copy from the client machine you can use \copy within psql. b Kevin Duffy wrote: > Hello: > > > > A follow up question that may clear this all up: > > > > Is the 'filename' relative to the server machine or the client where > pgAdmin is running? > > > > > > Kevin Duffy > > WR Capital Management > > 40 Signal Rd > > Stamford, CT > > 203-504-6221 > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
On Thu, 13 Nov 2008 09:11:05 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Teodor Sigaev <[EMAIL PROTECTED]> writes: > >> Yeah, I'm not convinced either. Still, Teodor's theory should > >> be easily testable: set synchronize_seqscans to FALSE and see > >> if the problem goes away. > > > Test suit to reproduce the problem: > > I don't doubt that you're describing a real effect, I'm just not > sure yet that it's the same thing Ivan is seeing. He seems to be > talking about more than 4x differences. Yes... 6min compared to something that span a night and is far more than what I'm willing to wait to give an exact measure since it does look to last more than the box itself. Anyway... I'll try Teodor's trick to see if somehow it can circumvent the real cause and I'll try everything on another box ASAP. thanks to all -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
Where to get ready binaries which can be copied and ran in Gentoo ? Is there any how-to pages or tips about compiling PostgreSql from source in this Gentoo ? This would do more harm than good so *don't*. How can compiling PostgreSql 8.3.x from source on Gentoo do more harm than good ? I expect that compiling form source operates on different environment which will not touch existing working 8.1.4 server Only shared resource is listening port (5432) and I can set it to some other for testing. No matter what you do you will have to dump the DB to upgrade it from 8.1.x anyway so some downtime is unavoidable. Depending on the contents you may even have to fix some bits of the schema since 8.3 is more strict than 8.1 in some ways. This is the hard part, and it has *nothing* to do with Gentoo; upgrading your installation is the easiest part and will only take a few minutes since it is completely automated (2 or 3 commands). Last shop will closed at 24:00 and first is opened at 6:30 am. So there is 6.5 hours allowed downtime in every night in this server. Client applicaton which uses this server and db schema works with lot of 8.2 and 8.3 servers. So I expect that server version change will be transparent to users. Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
On Wed, 12 Nov 2008 21:33:26 -0500, Greg Smith wrote: > You need to get one of the experimental builds that include slotted > support. Take a look at > http://overlays.gentoo.org/proj/postgresql/wiki/ExperimentalFAQ and the > page linked to by the blog article you mentioned at > http://dev-zero.ch/blog/archives/6-PostgreSQL-Status.html No, don't because those page are completely outdated. Slotted 8.3.4 is in regular portage, though marked for testing (~). It works fine, and so does updating those ebuilds to the very latest 8.3.5. > This looks like it's still in the early stages of release, so you might No. > packages are (not) maintained on Gentoo. You really should consider just > installing your own PostgreSQL from source rather than fooling with the > official pacakges. I would wager it will take you less time to figure out > how to do that than to fight these experimental packages into submission. Your information is very outdated. There is exactly no reason to build stuff yourself, especially since the OP doesn't seem to know what he's doing anyway. -h -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
no more mesages please.. Holger Hoffstaette escribió: On Thu, 13 Nov 2008 11:41:35 +0200, Andrus wrote: Greg, You need to get one of the experimental builds that include slotted support. Take a look at http://overlays.gentoo.org/proj/postgresql/wiki/ExperimentalFAQ and the page linked to by the blog article you mentioned at http://dev-zero.ch/blog/archives/6-PostgreSQL-Status.html Thank you. Please forget those pages, they are very outdated. I have SSH root access to this system over internet only and no experiences on Linux. If the system and/or the database are important, get someone who knows what they are doing. Person who installed this server has left the company. So I think it is not possible/reasonable to try change OS in this server. You don't have to do that - it would not solve anything. The main probem is the old PostgreSQL version, not the OS. Where to get ready binaries which can be copied and ran in Gentoo ? Is there any how-to pages or tips about compiling PostgreSql from source in this Gentoo ? This would do more harm than good so *don't*. No matter what you do you will have to dump the DB to upgrade it from 8.1.x anyway so some downtime is unavoidable. Depending on the contents you may even have to fix some bits of the schema since 8.3 is more strict than 8.1 in some ways. This is the hard part, and it has *nothing* to do with Gentoo; upgrading your installation is the easiest part and will only take a few minutes since it is completely automated (2 or 3 commands). If you are willing to pay for professional help feel free to email me. -h Aviso Legal Este mensaje puede contener informacion de interes solo para CVG Venalum. Solo esta permitida su copia, distribucion o uso a personas autorizadas. Si recibio este corre por error, por favor destruyalo. Eventualmentew los correos electonicos pueden ser alterados. Al respecto, CVG Venalum no se hace responsable por los errores que pudieran afectar al mensaje original. begin:vcard fn:Adriana Alfonzo n:Alfonzo;Adriana org;quoted-printable:Gcia. Sistemas y Organizaci=C3=B3n;Proy. Software Libre adr;quoted-printable:;;;Pto. Ordaz;Bol=C3=ADvar;;Venezuela email;internet:[EMAIL PROTECTED] title:Analista Ext. 5694 tel;work:5694 version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
Teodor Sigaev <[EMAIL PROTECTED]> writes: >> Yeah, I'm not convinced either. Still, Teodor's theory should be easily >> testable: set synchronize_seqscans to FALSE and see if the problem goes >> away. > Test suit to reproduce the problem: I don't doubt that you're describing a real effect, I'm just not sure yet that it's the same thing Ivan is seeing. He seems to be talking about more than 4x differences. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
On Thu, 13 Nov 2008 11:41:35 +0200, Andrus wrote: > Greg, > >> You need to get one of the experimental builds that include slotted >> support. Take a look at >> http://overlays.gentoo.org/proj/postgresql/wiki/ExperimentalFAQ and the >> page linked to by the blog article you mentioned at >> http://dev-zero.ch/blog/archives/6-PostgreSQL-Status.html > > Thank you. Please forget those pages, they are very outdated. > I have SSH root access to this system over internet only and no > experiences on Linux. If the system and/or the database are important, get someone who knows what they are doing. > Person who installed this server has left the company. So I think it is > not possible/reasonable to try change OS in this server. You don't have to do that - it would not solve anything. The main probem is the old PostgreSQL version, not the OS. > Where to get ready binaries which can be copied and ran in Gentoo ? Is > there any how-to pages or tips about compiling PostgreSql from source in > this Gentoo ? This would do more harm than good so *don't*. No matter what you do you will have to dump the DB to upgrade it from 8.1.x anyway so some downtime is unavoidable. Depending on the contents you may even have to fix some bits of the schema since 8.3 is more strict than 8.1 in some ways. This is the hard part, and it has *nothing* to do with Gentoo; upgrading your installation is the easiest part and will only take a few minutes since it is completely automated (2 or 3 commands). If you are willing to pay for professional help feel free to email me. -h -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
We could extend IndexBuildHeapScan's API to support that, but I'm not quite convinced that this is the issue. That extension might be useful for bitmap index too to simplify index creation process. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
Yeah, I'm not convinced either. Still, Teodor's theory should be easily testable: set synchronize_seqscans to FALSE and see if the problem goes away. Test suit to reproduce the problem: DROP TABLE IF EXISTS foo; DROP TABLE IF EXISTS footmp; CREATE OR REPLACE FUNCTION gen_array() RETURNS _int4 AS $$ SELECT ARRAY( SELECT (random()*1000)::int FROM generate_series(1,10+(random()*90)::int) ) $$ LANGUAGE SQL VOLATILE; SELECT gen_array() AS v INTO foo FROM generate_series(1,10); VACUUM ANALYZE foo; CREATE INDEX fooidx ON foo USING gin (v); DROP INDEX fooidx; SELECT * INTO footmp FROM foo LIMIT 9; CREATE INDEX fooidx ON foo USING gin (v); DROP INDEX fooidx; On my notebook with HEAD and default postgresql.conf it produce (show only interesting part): postgres=# CREATE INDEX fooidx ON foo USING gin (v); Time: 14961,409 ms postgres=# SELECT * INTO footmp FROM foo LIMIT 9; postgres=# CREATE INDEX fooidx ON foo USING gin (v); LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (8 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (7 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (10 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". LOG: checkpoints are occurring too frequently (8 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". CREATE INDEX Time: 56286,507 ms So, time for creation is 4-time bigger after select. Without "SELECT * INTO footmp FROM foo LIMIT 9;": postgres=# CREATE INDEX fooidx ON foo USING gin (v); CREATE INDEX Time: 13894,050 ms postgres=# CREATE INDEX fooidx ON foo USING gin (v); LOG: checkpoints are occurring too frequently (14 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". CREATE INDEX Time: 15087,348 ms Near to the same time. With synchronize_seqscans = off and SELECT: postgres=# CREATE INDEX fooidx ON foo USING gin (v); CREATE INDEX Time: 14452,024 ms postgres=# SELECT * INTO footmp FROM foo LIMIT 9; postgres=# CREATE INDEX fooidx ON foo USING gin (v); LOG: checkpoints are occurring too frequently (16 seconds apart) HINT: Consider increasing the configuration parameter "checkpoint_segments". CREATE INDEX Time: 14557,750 ms Again, near to the same time. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
On Wed, 12 Nov 2008 19:47:15 -0500, Guy Rouillier wrote: > To answer your question directly, you won't find a prepackaged solution to > running simultaneous version of PG (or any other software package) on > Gentoo. That's not how Gentoo is designed to be used. Having said that, You are contradicting yourself: > I remember reading about slots, which may allow what you are trying to do. > But I've never investigated. Slots are *exactly* the mechanism that enables multiple versions of the same package to be installed in parallel and it works fantastically well. However since not every package is easily slottable (such as the old and therefore unslotted postgres 8.1.x) it is not an option in Andrus' case, and also wouldn't solve the problem of upgrading the DB itself, which is purely a PostgreSQL problem on any platform. And FYI Gentoo supports binary packages just fine; in fact that's how you are supposed to install packages on larger installations. -h -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> I see. So this could explain Ivan's issue if his table contains >> large numbers of repeated GIN keys. Ivan, is that what your data >> looks like? > Well if by GIN keys you mean lexemes it could be. But I wouldn't say > this circumstance is uncommon among users of tsearch. I'd expect > other people had used tsearch2 to search through titles, authors and > publishers of books, so if that was the problem I'd expect the > problem to come up earlier. Yeah, I'm not convinced either. Still, Teodor's theory should be easily testable: set synchronize_seqscans to FALSE and see if the problem goes away. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] ERROR: incompatible library
"Tony Fernandez" <[EMAIL PROTECTED]> writes: > I am getting the following error: > :14: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: > incompatible library "/usr/lib/pgsql/xxid.so": missing magic block You need a version of xxid.so that matches your server version, on each server. It might well also be that you need a newer version of Slony --- I would not expect an 8.1-vintage release of Slony to work on 8.3. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored function - send array as parameter to stored function
brian <[EMAIL PROTECTED]> writes:
> Yes, this one got me, also. Strangely, you need to do:
> select iterate('{1,2}');
In reasonably modern versions of PG you could use an array constructor:
select iterate(array[1,2,3]);
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Suboptimal execution plan for simple query
Hi! Sam Mason wrote: > You may have some luck with increasing the statistics target on the > entry_id and last_updated columns and re-ANALYZING the table. Then > again, the fact that it thinks it's only going to get a single row > back when it searches for the entity_id suggests that it's all a bit > confused! Thank you for that suggestion. Increasing the statistics target on entity_id from the default 10 to 30 and re-analyzing did the trick: "Limit (cost=340.75..340.75 rows=1 width=12) (actual time=0.084..0.085 rows=1 loops=1)" " -> Sort (cost=340.75..341.00 rows=103 width=12) (actual time=0.081..0.081 rows=1 loops=1)" "Sort Key: last_updated" "-> Index Scan using idx_image_relation_entity_id on image_relation (cost=0.00..337.30 rows=103 width=12) (actual time=0.059..0.065 rows=3 loops=1)" " Index Cond: (entity_id = 69560)" "Total runtime: 0.121 ms" Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_lesslog/pg_standby
Hi folks, I was wondering on pg_lesslog integration on our architecture based on wal shipping using pg_standby (thanks Simon) as recovery tool. The main target is to reduce wal files accumulation on "master" host (thus consumming disk space) in case of slave unavailability. As far as I've read the source of pg_stanby, WAL size is checked in CustomizableNextWALFileReady function, allowing customization of file size check. Actually, the "legacy" way WAL is checked, is "if file size is normal WAL file size then we've got a supposed-good WAL". Thus, we cannot use compressed log files out of "pg_lesslog", we need to "decompress" it before processing, including a "pre-processing" stage and/or command. There are three ways to do such : - first, we could write a script on master to compress, rsync then decompress (on slave) file, but the idea to call a remote processing on slave seems to be quite hazardous to me (in some way it may lead to strange situations) - second, we could rsync compressed log to a directory, use a local "daemon" on slave to watch a directory and decompress files to final place (pg_standby WAL source directory) - third, we could add some functionnalities to pg_standby allowing to pre-process WAL file (thus customizing CustomizableNextWALFileReady function?), this might be usefull for other issues or use cases than pg_lesslog but it applies quite good to it :) What are your thoughts for each of these points? Thanks, -- Jean-Christophe Arnu
[GENERAL] DBI error when changing views
Hello, When changing a view in my mod_perl (mason) application I typically get this error if I don't restart apache: "DBD::Pg::st execute failed: ERROR: cached plan must not change result type" Is there a way to avoid having to restart apache? Thanks, -- http://www.critikart.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] still gin index creation takes forever
On Wed, 12 Nov 2008 15:18:05 -0500
Tom Lane <[EMAIL PROTECTED]> wrote:
> > So, in that case process can insert about 1000 ItemPointers per
> > one data tree lookup, in opposite case it does 1000 lookups in
> > data tree.
> I see. So this could explain Ivan's issue if his table contains
> large numbers of repeated GIN keys. Ivan, is that what your data
> looks like?
Well if by GIN keys you mean lexemes it could be. But I wouldn't say
this circumstance is uncommon among users of tsearch. I'd expect
other people had used tsearch2 to search through titles, authors and
publishers of books, so if that was the problem I'd expect the
problem to come up earlier.
Actually tsearch2 is not completely tuned up, since I still have to
"mix" Italian and English configuration to get rid of some more
stop words etc... that may increase the number of repetitions, but I
doubt this only put me in a corner case.
Anyway trying to answer in a more objective way to your question I
ran:
SELECT * FROM ts_stat('SELECT ft1idx FROM catalog_items') order by
nentry desc, ndoc desc limit 20;
It ran over 9h and I still wasn't able to get the answer.
I killed psql client that was running it and postgres continued to
eat 100% CPU for a while till I stopped it.
Considering that running:
SELECT * FROM ts_stat('SELECT ft1idx FROM catalog_items limit 5')
order by nentry desc, ndoc desc limit 20;
returned in less than 2 minutes and catalog_items has a bit less
than 1M record... there is still something weird.
"springer";10824;10833
"e";7703;8754
"di";6815;7771
"il";5622;6168
"la";4989;5407
"hall";4357;4416
"prentic";4321;4369
"l";3920;4166
"del";3092;3281
"edizioni";2465;2465
"della";2292;2410
"m";2283;2398
"dell";2150;2281
"j";1967;2099
"d";1789;1864
"per";1685;1770
"longman";1671;1746
"le";1656;1736
"press";1687;1687
"de";1472;1564
examining 90K records took a bit more than 6min.
I'll try to move everything on another box and see what happens.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Slony1-general] ERROR: incompatible library
--- On Wed, 12/11/08, Tony Fernandez <[EMAIL PROTECTED]> wrote: > Date: Wednesday, 12 November, 2008, 10:52 PM > Hello lists, > > > > I am trying to run Slony on a Master Postgres 8.1.11 > replicating to a > Slave same version and 2nd Slave Postgres 8.3.4. > > I am getting the following error: > > > > :14: PGRES_FATAL_ERROR load > '$libdir/xxid'; - ERROR: > incompatible library "/usr/lib/pgsql/xxid.so": > missing magic block > > HINT: Extension libraries are required to use the > PG_MODULE_MAGIC > macro. > > :14: Error: the extension for the xxid data > type cannot be loaded > in database 'dbname=hdap host=10.0.100.234 port=6543 > user=myuser > password=mp' I think you've proabably built slony against one version of postgres and then tried to use it with another. You must build against 8.1.11 and then separately for 8.3.4, using the same version of slony ofcourse. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Suboptimal execution plan for simple query
On Wed, Nov 12, 2008 at 04:15:23PM +0100, Markus Wollny wrote: > I've got this simple query > > SELECT image_id > FROM image_relation > WHERE entity_id = 69560::integer > ORDER BY last_updated DESC > LIMIT1; > > which currently runs for something around 600ms. Here's the explain analyze > output: > > "Limit (cost=0.00..144.78 rows=1 width=12) (actual time=599.745..599.747 > rows=1 loops=1)" > " -> Index Scan Backward using idx_image_relation_last_updated on > image_relation (cost=0.00..39525.70 rows=273 width=12) (actual > time=599.741..599.741 rows=1 loops=1)" > "Filter: (entity_id = 69560)" > "Total runtime: 599.825 ms" The database would appear to be thinking that it's better off running through time backwards to find the entry than searching for the entry directly. This is normally because each entry_id has several rows and running through time would end up doing less work (especially as it wouldn't need to sort the results afterwards). You may have some luck with increasing the statistics target on the entry_id and last_updated columns and re-ANALYZING the table. Then again, the fact that it thinks it's only going to get a single row back when it searches for the entity_id suggests that it's all a bit confused! Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
Greg, You need to get one of the experimental builds that include slotted support. Take a look at http://overlays.gentoo.org/proj/postgresql/wiki/ExperimentalFAQ and the page linked to by the blog article you mentioned at http://dev-zero.ch/blog/archives/6-PostgreSQL-Status.html Thank you. I have SSH root access to this system over internet only and no experiences on Linux. Person who installed this server has left the company. So I think it is not possible/reasonable to try change OS in this server. You really should consider just installing your own PostgreSQL from source rather than fooling with the official pacakges. I would wager it will take you less time to figure out how to do that than to fight these experimental packages into submission. Where to get ready binaries which can be copied and ran in Gentoo ? Is there any how-to pages or tips about compiling PostgreSql from source in this Gentoo ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can someone help me to make a sql more pretty and more concise?
thanks for ur help, but, I think that is not my want:D if you use sum like this, it must be with group by, what I want is do sum on all columns, not group by. thanks regards, 在 2008-11-13四的 19:27 +1100，Russell Smith写道： > Yi Zhao wrote: > > I want to select some column(a, b) from the table with the specified > > condition, so, i can do like this: > > select a, b from mytable where id = (select id from temptable where > > tname = 'df' ) and stype = 'def' and range = 'afk' > > > How about; > > SELECT a, b, count(1), sum(c) FROM mytable WHERE id = (select id from > temptable where > tname = 'df' ) and stype = 'def' and range = 'afk' GROUP BY 1,2; > > Russell. > > but, I want the result contains a sum(c) and a count value extra, > > so, I use the sql below: > > select a, b, > > (select count(1) from mytable where id = > > ( > > select id from temptable where tname = 'df' > > ) and stype = 'def' and range = 'afk' > > ), > > (select sum(c) from mytable where id = > > ( > > select id from temptable where tname = 'df' > > ) and stype = 'def' and range = 'afk' > > ) > > from mytable where id = ( > > select id from temptable where tname = 'df' > > ) and stype = 'def' and range = 'afk'; > > > > can someone help me to make this sql statement above more pretty and more > > concise? > > > > > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Suboptimal execution plan for simple query
Hi! In preparation for my upcoming upgrade to PostgreSQL 8.3.5, I have taken the opportunity to try this scenario on a test machine with the latest PostgreSQL version. Unfortunately the result remains the same, though this database has been just reloaded from a dump and vacuum analyzed. select version() outputs "PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)". Kind regards Markus Just for reference: > Now I've got this simple query > > SELECT image_id > FROM image_relation > WHERE entity_id = 69560::integer > ORDER BY last_updated DESC > LIMIT1; > > which currently runs for something around 600ms. Here's the explain > analyze output: > > "Limit (cost=0.00..144.78 rows=1 width=12) (actual > time=599.745..599.747 rows=1 loops=1)" " -> Index Scan Backward > using idx_image_relation_last_updated on image_relation > (cost=0.00..39525.70 rows=273 width=12) (actual time=599.741..599.741 > rows=1 loops=1)" "Filter: (entity_id = 69560)" "Total > runtime: 599.825 ms" > SELECT image_id > FROM image_relation > WHERE entity_id = 69560 > AND entity_id = entity_id > ORDER BY last_updated DESC > LIMIT1 > > "Limit (cost=881.82..881.82 rows=1 width=12) (actual > time=0.097..0.099 rows=1 loops=1)" " -> Sort (cost=881.82..881.82 > rows=1 width=12) (actual time=0.094..0.094 rows=1 loops=1)" " > Sort Key: last_updated" "-> Index Scan using > idx_image_relation_entity_id on image_relation (cost=0.00..881.81 > rows=1 width=12) (actual time=0.063..0.075 rows=3 loops=1)" " > Index Cond: (entity_id = 69560)" " Filter: (entity_id = > entity_id)" "Total runtime: 0.128 ms" Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can someone help me to make a sql more pretty and more concise?
Yi Zhao wrote: > I want to select some column(a, b) from the table with the specified > condition, so, i can do like this: > select a, b from mytable where id = (select id from temptable where > tname = 'df' ) and stype = 'def' and range = 'afk' > How about; SELECT a, b, count(1), sum(c) FROM mytable WHERE id = (select id from temptable where tname = 'df' ) and stype = 'def' and range = 'afk' GROUP BY 1,2; Russell. > but, I want the result contains a sum(c) and a count value extra, > so, I use the sql below: > select a, b, > (select count(1) from mytable where id = > ( > select id from temptable where tname = 'df' > ) and stype = 'def' and range = 'afk' > ), > (select sum(c) from mytable where id = > ( > select id from temptable where tname = 'df' > ) and stype = 'def' and range = 'afk' > ) > from mytable where id = ( > select id from temptable where tname = 'df' > ) and stype = 'def' and range = 'afk'; > > can someone help me to make this sql statement above more pretty and more > concise? > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
