Re: [PERFORM] database bloat,non removovable rows, slow query etc...

restart Pg. Once restarted we were able to do a VACUUM FULL and this took care of the issue. hth Patrick Hatcher Development Manager Analytics/MIO Macys.com Matteo Sgalaberni

Re: [PERFORM] database model tshirt sizes

We have size and color in the product table itself. It is really an attribute of the product. If you update the availability of the product often, I would split out the quantity into a separate table so that you can truncate and update as needed. Patrick Hatcher Development Manager Analytics

Re: [PERFORM] Slow query. Any way to speed up?

09 rows=64 width=8) (actual time=93.710..362.802 rows=63 loops=1) Index Cond: ((date_dim_id >= '2005-10-30'::date) AND (date_dim_id <= '2005-12-31'::date)) Total runt

[PERFORM] Slow query. Any way to speed up?

NULL, calendar_month int2 NOT NULL, julian_day int2 NOT NULL, CONSTRAINT date_dimph PRIMARY KEY (date_dim_id) ) WITH OIDS; -- Index: amc_weekid_idx -- DROP INDEX amc_weekid_idx; CREATE INDEX amc_weekid_idx ON date_dim USING btree (amc_week_id); -- Index: date_date_i

Re: [PERFORM] slow update

Thanks. No foreign keys and I've been bitten by the mismatch datatypes and checked that before sending out the message :) Patrick Hatcher Development Manager Analytics/MIO Macys.com Tom

[PERFORM] slow update

loat8 ) WITHOUT OIDS; Patrick Hatcher Development Manager Analytics/MIO Macys.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

[PERFORM] Poor SQL performance

Hey there folks. I'm at a loss as to how to increase the speed of this query. It's something I need to run each day, but can't at the rate this runs. Tables are updated 1/day and is vacuum analyzed after each load. select ddw_tran_key, r.price_type_id, t.price_type_id from cdm.cdm_ddw_tran_

Re: [PERFORM] Slow update statement

At the time this was the only process running on the box so I set sort_mem= 228000; It's a 12G box. Tom Lane wrote: Patrick Hatcher <[EMAIL PROTECTED]> writes: Hash Join (cost=1246688.42..4127248.31 rows=12702676 width=200) Hash Cond: ("outer".cus_num = "inne

Re: [PERFORM] Slow update statement

t8, CONSTRAINT ddwcus_pk PRIMARY KEY (cus_nbr) ) WITH OIDS; CREATE INDEX cdm_ddwcust_id_idx ON cdm.cdm_ddw_customer USING btree (cus_nbr); CREATE TABLE cdm.bcp_ddw_ck_cus ( cus_num int8, indiv_fkey int8 NOT NULL ) WITHOUT OIDS; Tom Lane wrote: Patrick Hatcher <[EMAIL PROTECTED]> wr

Re: [PERFORM] Slow update statement

;.cus_nbr) -> Seq Scan on bcp_ddw_ck_cus b (cost=0.00..195690.76 rows=12702676 width=16) -> Hash (cost=874854.34..874854.34 rows=12880834 width=192) -> Seq Scan on cdm_ddw_customer (cost=0.00..874854.34 rows=12880834 width=192) John A Meinel wrote: Patrick Hatcher

[PERFORM] Slow update statement

[Reposted from General section with updated information] Pg 7.4.5 I'm running an update statement on about 12 million records using the following query: Update table_A set F1 = b.new_data from table_B b where b.keyfield = table_A.keyfield both keyfields are indexed, all other keys in table_A we

[PERFORM] Sluggish server performance

ost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) Patrick Hatcher ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Improve BULK insertion

I do mass inserts daily into PG.  I drop the all indexes except my primary key and then use the COPY FROM command.  This usually takes less than 30 seconds.  I spend more time waiting for indexes to recreate.Patrick HatcherMacys.Com [EMAIL PROTECTED] wrote: -To: [EMAIL PROTECTED]From: Christoph

[PERFORM] determining max_fsm_pages

Pg: 7.4.5 8G ram 200G RAID5 I have my fsm set as such: max_fsm_pages = 30 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 500 # min 100, ~50 bytes each I just did a vacuum full on one table and saw this result: INFO: analyzing "cdm.cdm_fed_agg_purch" INFO: "cdm_

Re: [PERFORM] Slow update/insert process

Thanks for the help. I found the culprit.  The user had created a function within the function ( pm.pm_price_post_inc(prod.keyp_products)). Once this was fixed the time dropped dramatically. Patrick Hatcher Macys.Com Legacy Integration Developer 415-422-1610 office HatcherPT - AIM Patrick

[PERFORM] Slow update/insert process

2                # min 4, 8KB each # - Checkpoints - checkpoint_segments = 50        # in logfile segments, min 1, 16MB each checkpoint_timeout = 600        # range 30-3600, in seconds #checkpoint_warning = 30        # 0 is off, in seconds #commit_delay = 0               # range 0-10, in microseconds #commit_siblings = 5            # range 1-1000 Patrick Hatcher Macys.Com

Re: [PERFORM] vacuum full & max_fsm_pages question

I upgraded to 7.4.3 this morning and did a vacuum full analyze on the problem table and now the indexes show the correct number of records Patrick Hatcher Macys.Com Josh Berkus <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 09/21/04 10:49 AM To "Patrick Hatcher" &

Re: [PERFORM] vacuum full & max_fsm_pages question

Nope. It's been running like a champ for while now. Patrick Hatcher Macys.Com Legacy Integration Developer 415-422-1610 office HatcherPT - AIM Josh B

Re: [PERFORM] vacuum full & max_fsm_pages question

uot;Robert Treat" <[EMAIL PROTECTED]> To: "Patrick Hatcher" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 20, 2004 11:12 PM Subject: Re: [PERFORM] vacuum full & max_fsm_pages question > On Tuesday 21 September 2004 00:01, Patrick Hatcher wr

[PERFORM] vacuum full & max_fsm_pages question

  Hello. Couple of questions:     - Q1: Today I decided to do a vacuum full verbose analyze on a large table that has been giving me slow performance.  And then I did it again.  I noticed that after each run the values in my indexes and estimate row version changed.  What really got me wond

Re: [PERFORM] vacuum full 100 mins plus?

. Patrick Hatcher <[EMAIL PROTECTED] om>To S

[PERFORM] vacuum full 100 mins plus?

w versions) is 4474020460 bytes. 544679 pages are or will become empty, including 0 at the end of the table. 692980 pages containing 4433398408 free bytes are potential move destinations. CPU 29.55s/4.13u sec elapsed 107.82 sec. TIA Patrick Hatcher ---

Re: [PERFORM] Slow vacuum performance

Thanks! Patrick Hatcher Andrew McMillan <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/21/04 03:11 AM To Patrick Hatcher <[EMAIL PROTECTED]> cc [EMAIL PROTECTED] Subject Re: [PERFORM] Slow vacuum performance On Fri, 2004-06-18 at 19:51 -0700, Patrick H

[PERFORM] Slow vacuum performance

21 but wanted to make sure shared_buffers = 2000 # min 16, at least max_connections*2, 8KB each sort_mem = 12288# min 64, size in KB # - Free Space Map - max_fsm_pages = 10 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100

[PERFORM] Upgrading question (recycled transaction log)

in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 TIA Patrick Hatcher ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] *very* slow query to summarize data for a month ...

Do you have an index on ts.bytes? Josh had suggested this and after I put it on my summed fields, I saw a speed increase. I can't remember the article was that Josh had written about index usage, but maybe he'll chime in and supply the URL for his article. hth Patri

Re: [PERFORM] *very* slow query to summarize data for a month ...

here's the URL: http://techdocs.postgresql.org/techdocs/pgsqladventuresep2.php Patrick Hatcher Macys.Com Legacy Integration Developer 415-422-1610 office HatcherPT - AIM Pa

Re: [PERFORM] SELECT's take a long time compared to other DBMS

, but the larger the recordset the slower the data is return to the client. I played around with the cache size on the driver and found a value between 100 to 200 provided good results. HTH Patrick Ha

Re: [PERFORM] Memory question

Thank you Patrick Hatcher "scott.ma

Re: [PERFORM] Memory question

again Patrick Hatcher Josh Berkus

[PERFORM] Memory question

ce, 100.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K buff Swap: 2044056K av, 0K used, 2044056K free 6257620K cached Patrick Hatcher ---(e