Re: [PERFORM] Postgresql in a Virtual Machine

ing performance, the benefits in these areas are many when you administrate many servers/databases. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE-

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

e University. I will send them this information and they can decide if they want to rewrite the statement or use the OFFSET trick. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/2013 06:10 AM, Jeff Janes wrote: > On Monday, August 26, 2013, Rafael Martinez wrote: Hei > > Could you do explain (analyze, buffers) of these? > With 9.1: http://explain.depesz.com/s/FMe with 9.2: http://explain.depe

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

ilmparticipation | 232 MB | public | filmparticipationpkey | index | postgres | filmparticipation | 232 MB | public | personlastnameindex| index | postgres | person | 41 MB | public | personpkey | index | postgres | person | 37 MB |

Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/26/2013 02:33 PM, Rafael Martinez wrote: [] > The SQL statement is: > > SELECT firstname || ' ' || lastname AS Name FROMPerson R WHERE > R.gender like 'F' AND 19 < (SELECT COUNT(DISTINCT

[PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1

enegotiation_limit | 0 wal_buffers | 16MB wal_level | archive wal_sync_method | fdatasync work_mem| 16MB Any ideas on why this is happening and how to fix it? Thanks in advance for your time. regards, - -- Rafael Martinez Guer

[PERFORM] timing != log duration

: duration: 0.055 ms statement: DEALLOCATE foo; - --- Thanks in advance regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10

[PERFORM] DBD-Pg prepared statement versus plain execution

we assume that running this application without using prepared statements will do that it runs faster the time used by parse+bind+deallocate? Thanks in advance. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.

Re: [PERFORM] Dramatic change in memory usage with version 9.1

not have anything to do with this bug (it was introduced in 9.1.2) We could not finish a full import of some of our databases with 9.1.2 because all ram+swap was used in a matter of minuttes. We are using 9.1.1 and we haven't seen the 9.1.2 behavior. regards, - -- Rafael Martinez Guerrero

Re: [PERFORM] Dramatic change in memory usage with version 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/21/2011 12:48 AM, Craig Ringer wrote: > On 19/12/2011 11:04 PM, Rafael Martinez wrote: >> Any ideas about why this dramatic change in memory usage when the only >> thing apparently changed from our side is the postgres versio

Re: [PERFORM] Dramatic change in memory usage with version 9.1

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/20/2011 12:15 PM, Cédric Villemain wrote: > Le 19 décembre 2011 16:04, Rafael Martinez a écrit > : >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hello >> >> I am sending this email to ask

[PERFORM] Dramatic change in memory usage with version 9.1

, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

Re: [PERFORM] WAL partition filling up after high WAL activity

coding of huge attachments). When we generated the right tsvector data, the creation of the GIN index ran smoothly and the problem with extra WAL files disappeared. PS.- In our case, the disk space used by all the extra WAL files was almost the equivalent to the 17GB of our GIN index. regards, - -- Ra

Re: [PERFORM] raid setup for db

Thomas Finneid wrote: > Hi > > I am wondering what stripe size, on a raid 0, is the most suitable for > postgres 8.2? > Hello Raid 0 for a database? This is a disaster waiting to happen. Are you sure you want to use raid0? regards -- Rafael Martinez, Center for Informa

Re: [PERFORM] Query planner making bad decisions

Filter: ((customfields_3.name)::text = 'QA Group Code'::text) -> Index Scan using customfields_pkey on customfields customfields_1 (cost=0.00..1.94 rows=1 width=4) (never executed) Index Cond: (customfields_1.id = objectcustomfieldvalues_

Re: [PERFORM] Performance problems deleting data

Tom Lane wrote: > Rafael Martinez <[EMAIL PROTECTED]> writes: > >> Any ideas why it is taking 2462558.813 ms to finish when the total time >> for the deletion is 2.546 ms + 3.422 ms + 0.603ms? > Hei Tom, I got this information from my colleague: > Is the problem r

[PERFORM] Performance problems deleting data

ot directly releated tables to 'module' could lock the deletion without showing in EXPLAIN ANALYZE?. The two 'Trigger for constraint' in the EXPLAIN ANALYZE output only show two tables having an attribute as a foreign key in 'module', but if these two tables have to wait fo

Re: [PERFORM] Need to run CLUSTER to keep performance

Rafael Martinez wrote: > Heikki Linnakangas wrote: > >> In 8.1, CLUSTER will remove those tuples anyway, but it's actually not >> correct. > > With other words, we have to be very carefull to not run CLUSTER on > a table been modified inside a transaction

Re: [PERFORM] Need to run CLUSTER to keep performance

Heikki Linnakangas wrote: > Rafael Martinez wrote: >> DETAIL: 83623 dead row versions cannot be removed yet. > > Looks like you have a long-running transaction in the background, so > VACUUM can't remove all dead tuples. I didn't see that in the vacuum > verbo

Re: [PERFORM] Need to run CLUSTER to keep performance

Rafael Martinez wrote: > > We have more information about this 'problem'. > Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of rem

Re: [PERFORM] Need to run CLUSTER to keep performance

Tom Lane wrote: > Rafael Martinez <[EMAIL PROTECTED]> writes: >> Heikki Linnakangas wrote: >>> On a small table like that you could run VACUUM every few minutes >>> without much impact on performance. That should keep the table size in >>> check. > >

Re: [PERFORM] Need to run CLUSTER to keep performance

all the > unused space on pages. You need to run CLUSTER or VACUUM FULL once to > shrink the relation, but after that frequent-enough VACUUMs should keep > the table size down. > Ok, thanks for the advice. We will try this and will come back with more information. -- Rafael Martin

Re: [PERFORM] Need to run CLUSTER to keep performance

Alvaro Herrera wrote: > Rafael Martinez wrote: >> The 'problem' is that performance decrease during the day and the only >> thing that helps is to run CLUSTER on the table with problems. VACUUM >> ANALYZE does not help. > > Probably because all the live t

Re: [PERFORM] Need to run CLUSTER to keep performance

Heikki Linnakangas wrote: > Rafael Martinez wrote: >> The tables with this 'problem' are not big, so CLUSTER finnish very fast >> and it does not have an impact in the access because of locking. But we >> wonder why this happens. > > 2 seconds for seq scann

[PERFORM] Need to run CLUSTER to keep performance

200 page slots, 4000 relations, using 12131 KB. -- The tables with this 'problem' are not big, so CLUSTER finnish very fast and it does not have an impact in the access because of locking. But we wonder why this happens. Do you ne

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

#maintenance_work_mem = 16MB# min 1MB fsync = off # turns forced synchronization on or off #effective_cache_size = 128MB [] -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo,

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: > Rafael Martinez wrote: > > On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: > > > >> Stephan Szabo wrote: > >> > >>> On Wed, 6 Dec 2006, Rafael Martinez wrote: > >>>

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: > Stephan Szabo wrote: > > On Wed, 6 Dec 2006, Rafael Martinez wrote: > >> > >> mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, > >> mail_received mr where mr.mail_id = m.ma

[PERFORM] Problems with an update-from statement and pg-8.1.4

_queue_id_index" btree (queue_id) Foreign-key constraints: "$1" FOREIGN KEY (mail_id) REFERENCES mail(mail_id) - Thanks in advance. regards, -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Informati

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

On Fri, 2006-04-07 at 13:36 -0400, Tom Lane wrote: > I wrote: > > Rafael Martinez Guerrero <[EMAIL PROTECTED]> writes: > >> I have a sql statement that takes 108489.780 ms with 8.0.7 in a > >> RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

_size, they don't matter in this > case. > The problem is not the amount of memory. It works much faster with only 16M and 7.4.12 than 8.0.7. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo,

Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and

On Fri, 2006-04-07 at 15:31, Richard Huxton wrote: > Rafael Martinez Guerrero wrote: > > Hello > > > > I have a sql statement that takes 108489.780 ms with 8.0.7 in a > > RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / > > 8GB RAM and only 4

[PERFORM] Same SQL, 104296ms of difference between 7.4.12 and 8.0.7

ncipals_1 (cost=0.00..4399.08 rows=168394 width=4) (actual time=0.026..412.688 rows=168409 loops=1) Filter: ((disabled = 0::smallint) AND (id <> 1)) -> Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupme

Re: [PERFORM] Help with optimizing a sql statement

On Thu, 2006-02-09 at 18:22 -0500, Tom Lane wrote: > Rafael Martinez Guerrero <[EMAIL PROTECTED]> writes: > > WHERE ((ACL_2.RightName = 'OwnTicket')) > > AND ((CachedGroupMembers_4.MemberId = Principals_1.id)) > > AND ((Groups_3.id = CachedGroupMember

Re: [PERFORM] Help with optimizing a sql statement

times every hour, so yes, statistics are up-to-date. I will increase default_statistics_target tomorrow at work and see what happens. Thanks for your help. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: ht

[PERFORM] Help with optimizing a sql statement

; btree ("type", instance, "domain") ******** rttest=# \d cachedgroupmembers" Table "public.cachedgroupmembers" Column | Type | Modifiers ---+--+--