[PERFORM] Confusing Query Performance

Hi, We have a table called "table1" which contains around 638725448 records. We created a subset of this table and named it as "new_table1" which has around 120107519 records. "new_table1" is 18% of the the whole "table1". If we fire the below queries we are not finding any drastic performance g

[PERFORM] Statement Timeout at User Level

Hi, Is it possible to put Statement timeout at User Level. Like If i have a user like 'guest', Can i put a statement timeout for it. -- Regards Gauri

Re: [PERFORM] Cross Join Problem

Thanx alot... its solved my problem On Mon, Aug 18, 2008 at 8:50 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > [ please keep the list cc'd for the archives' sake ] > > "Gauri Kanekar" <[EMAIL PROTECTED]> writes: > > On Mon, Aug 18, 2008 at 7:32 PM,

[PERFORM] Cross Join Problem

Hi, Following is the Query : SELECT sum(id), sum(cd), sum(ad) FROM table1 a , table2 b cross join table3 c WHERE a.nkey = b.key AND a.dkey = c.key AND c.date = '2008-02-01' AND b.id = 999 ; We have fired this on our production system which is

[PERFORM] Partitioned Tables Foreign Key Constraints Problem

Hi all, Have a problem related to partition tables. We have the following schema's : master - id integer (PRIMARY KEY) cid integer child1, child2 are the child tables of master table. Here "cid" is the field used for partitioning. We have another table called other_tbl - id integer (PRIMARY KE

Re: [PERFORM] Hot Issue

at 8:10 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Jonah H. Harris" <[EMAIL PROTECTED]> writes: > > On Wed, Jul 2, 2008 at 9:44 AM, Gauri Kanekar > >> Is there a way to check if the page is fill and the update is going on a > new > >> page ?? &

Re: [PERFORM] Hot Issue

ok.. But we have set fill_factor = 80 for all the indexes on table1. Is there a way to check if the page is fill and the update is going on a new page ?? On Wed, Jul 2, 2008 at 6:59 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > On Wed, Jul 2, 2008 at 9:11 AM, Gauri Kanekar

Re: [PERFORM] Hot Issue

How does it indicate if the entries qualify for hot update ?? hot have a limitation that it do not work if, the index column is updated. But that not the case over here. On Wed, Jul 2, 2008 at 6:38 PM, Devrim GÜNDÜZ <[EMAIL PROTECTED]> wrote: > On Wed, 2008-07-02 at 18:01 +0530, Gaur

Re: [PERFORM] Hot Issue

No, Vacuum Full was not done, but auto_vacuum did click onto table1. No cluster. On Wed, Jul 2, 2008 at 6:10 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > On Wed, Jul 2, 2008 at 8:31 AM, Gauri Kanekar > <[EMAIL PROTECTED]> wrote: > > Performance of Hot was much bett

[PERFORM] Hot Issue

Hi all, We have upgraded our database server to postgres 8.3.1 on 28th June. Checked out the performance of Hot on 30th June : relname n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup *table1* *15509156* *2884653* *0* *2442823* * 15509158* *68046* ta

[PERFORM] Installation Steps to migrate to Postgres 8.3.1

Hi, We want to migrate from postgres 8.1.3 to postgres 8.3.1. Can anybody list out the installation steps to be followed for migration. Do we require to take care of something specially. Thanks in advance ~ Gauri

[PERFORM] Pros and Cons of 8.3.1

Hi, Can anyone who have started using 8.3.1 list out the pros and cons. Thanx in advance ~ Gauri

Re: [PERFORM] Replication Syatem

We have tried fillfactor for indices and it seems to work. Need to try fillfactor for table. May for that reason the bulk update queries don't get the advantage of HOT :) On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee <[EMAIL PROTECTED]> wrote: > On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <[EMAI

Re: [PERFORM] Replication Syatem

ble1 set delta1 = 100 where code/100 =999; On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar <[EMAIL PROTECTED]> wrote: > fillfactor is set to 80 as you suggested. > delta* fields r updated and these fields are no where related to any of > the index fields. > > > > On

Re: [PERFORM] Replication Syatem

TED]> wrote: > On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar > <[EMAIL PROTECTED]> wrote: > > HOT doesn't seems to be working in our case. > > > > Can you please post output of the following query ? > > SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_u

Re: [PERFORM] Replication Syatem

.. but nothing seems to work. ~Gauri On Tue, Apr 29, 2008 at 10:18 PM, Shane Ambler <[EMAIL PROTECTED]> wrote: > Alvaro Herrera wrote: > > > Gauri Kanekar escribió: > > > > Do we need to do any special config changes or any other setting for > >

Re: [PERFORM] Replication Syatem

Thats how our updates works. We usually tend to touch the same row many times a day. ~ Gauri On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee <[EMAIL PROTECTED]> wrote: > On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar > <[EMAIL PROTECTED]> wrote: > > > > >

Re: [PERFORM] Replication Syatem

ze ==> 3584.66 Found that the size increased gradually. Is HOT working over here ?? Guide me if im doing something wrong. ~ Gauri On Tue, Apr 29, 2008 at 4:55 PM, Pavan Deolasee <[EMAIL PROTECTED]> wrote: > On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar > <[EMAIL PROTECTED]&g

Re: [PERFORM] Replication Syatem

to make HOT working?? ~ Gauri On Tue, Apr 29, 2008 at 2:07 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Tue, 29 Apr 2008, Gauri Kanekar wrote: > > We do vacuum full, as vacuum verbose analyse dont regain space for us. > > > > Ah, now we're getting to the root of

Re: [PERFORM] Replication Syatem

Andrew, Can you explain me in detail why u said vacuum full is making the things worst. We do vacuum full, as vacuum verbose analyse dont regain space for us. ~ Gauri On Mon, Apr 28, 2008 at 9:52 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > On Mon, Apr 28, 2008 at 07:35:37PM +05

Re: [PERFORM] Replication Syatem

But unless we do full vacuum the space is not recovered. Thats y we prefer full vacuum. ~ Gauri On Tue, Apr 29, 2008 at 10:38 AM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Tue, 29 Apr 2008, Gauri Kanekar wrote: > > Basically we have some background process which updates

Re: [PERFORM] Replication Syatem

off. Thats the reason we need to get the site down. ~ Gauri On Tue, Apr 29, 2008 at 3:13 AM, Chris Browne <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] ("Gauri Kanekar") writes: > > We have a table "table1" which get insert and updates daily in high > >

Re: [PERFORM] Replication Syatem

Salman, Slony don't do automatic failover. And we would appreciate a system with automatic failover :( ~ Gauri On Mon, Apr 28, 2008 at 7:46 PM, salman <[EMAIL PROTECTED]> wrote: > Gauri Kanekar wrote: > > > Peter, > > > > We are doing vacuum full every alter

Re: [PERFORM] Replication Syatem

TED]> wrote: > > On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote: > > Peter, > > > > We are doing vacuum full every alternate day. We also do vacuum > > analyze very often. > > We are currently using 8.1.3 version. > > Auto vacuum is already on. But

Re: [PERFORM] Replication Syatem

y you, thats y we reached to the decision of having a replication sytsem. So any suggestion on that :). Thanx ~ Gauri On Mon, Apr 28, 2008 at 7:28 PM, Peter Childs <[EMAIL PROTECTED]> wrote: > > > 2008/4/28 Gauri Kanekar <[EMAIL PROTECTED]>: > > All, > > > > W

[PERFORM] Replication Syatem

All, We have a table "table1" which get insert and updates daily in high numbers, bcoz of which its size is increasing and we have to vacuum it every alternate day. Vacuuming "table1" take almost 30min and during that time the site is down. We need to cut down on this downtime.So thought of havin

[PERFORM] Query Analyser

Hi List, Is there anyway so as to indicate the Query Analyser not to use the plan which it is using regularly, and use a new plan ? From where do the Query Analyser gets the all info to prepare a plan? Is it only from the pg_statistics table or are there anyother tables which have this info. s

[PERFORM] Performance Problem

Hi, explain analyze SELECT am.campaign_id, am.optimize_type, am.creative_id, am.optimize_by_days, am.impressions_delta, am.clicks_delta, am.channel_code, am.cost,dm.allocation_map_id, SUM(CASE dm.sqldate when 20070602 then dm.impressions_delivered else 0 end) as deliv_yest, SUM(CASE sign(20070526

[PERFORM] Performance Problem

Hi, explain analyze SELECT am.campaign_id, am.optimize_type, am.creative_id, am.optimize_by_days, am.impressions_delta, am.clicks_delta, am.channel_code, am.cost,dm.allocation_map_id, SUM(CASE dm.sqldate when 20070602 then dm.impressions_delivered else 0 end) as deliv_yest, SUM(CASE sign(20070526

Re: [PERFORM] Nested Loop

rd.sqldate BETWEEN '12/1/2006' AND '12/30/2006' AND ( rn.id IN ( 607 ) ) GROUP BY rd.sqldate, rs.id, rs.name, ra.id, ra.name, rc.id, rc.name, rc.rev_type, rc.act_type, rpt_chn.id, rpt_chn.name, rpt_cre.dn; On 3/26/07, Ragnar <[EMAIL PROTECTED]> wrote: On mán, 2007

Re: [PERFORM] Nested Loop

ops=1) Total runtime: 441153.878 ms (32 rows) we are using 8.2 version On 3/26/07, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Mon, Mar 26, 2007 at 05:34:39PM +0530, Gauri Kanekar wrote: > how to speedup nested loop queries and by which parameters. Please post a query you're tryi

[PERFORM] Nested Loop

Hi List, how to speedup nested loop queries and by which parameters. -- Regards Gauri

[PERFORM] When the Record Got Updated.

Hi List, Can i find out the timestamp when last a record from a table got updated. Do any of the pg system tables store this info. -- Regards Gauri

Re: [PERFORM] Server Startup Error

Thanks, But how to start postgres server On 2/26/07, Rodrigo Gonzalez <[EMAIL PROTECTED]> wrote: Gauri Kanekar wrote: > Hi List, > > Machine was down due to some hardware problem. > > After then when i issue this command /usr/local/pgsql/bin/psql -l > its givin

[PERFORM] Server Startup Error

Hi List, Machine was down due to some hardware problem. After then when i issue this command /usr/local/pgsql/bin/psql -l its giving me the following error psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix

[PERFORM] Query Planner

Hi List, I have a Query. So when i do explain analyse on it , it shows me many Hash Joins. So is it possible to indicate the Query Planner not to consider Hash Join. -- Regards Gauri

Re: [PERFORM] Not Picking Index

I want the planner to ignore a specific index. I am testing some query output. For that purpose i dont want the index. I that possible to ignore a index by the planner. On 2/16/07, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Feb 16, 2007 at 06:26:51PM +0530, Gauri Kanekar wrote: &g

[PERFORM] Not Picking Index

Hi List, I want to run a Select Query on a table. But i dont want the query to pick a index defined on that table. So can i instruct the planner not to pick that index. -- Regards Gauri

[PERFORM] Auto Vacuum Problem

Hi List, When auto vacuum is over the dead tuple are seems to get reduced, but physical size of database do not decreases. We are using Postgres 8.1.3 and this are the auto vacuum settings. autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 900#

Re: [PERFORM] Vacuum v/s Autovacuum

Hi Thanks. We have autovacuum ON , but still postgres server warns to increas max_fsm_pages value. Do autovacuum release space after it is over? so how can we tackle it. On 1/18/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Jan 18, 2007, at 22:24 , Gauri Kanekar wrote:

[PERFORM] Vacuum v/s Autovacuum

Hi List, Can anybody help me out with this - is autovacuum similar to vacuum full analyse verbose. -- Regards Gauri

[PERFORM] Version Change

Hi List, Can anybody suggest some comprehensive test for version change from 8.1.3 to 8.2 -- Thanks in advance Gauri

[PERFORM] Table Size

Hi, Can anybody help me out to get following info of all the tables in a database. table_len tuple_count tuple_len tuple_percent dead_tuple_count dead_tuple_len dead_tuple_percent free_space free_percent Thanks Gauri

[PERFORM] Partitioning

Can anybody help me out I just wanted to knw what will be the configuraion settings for partitioning table so as to make inserts faster on the partitioned tables. -- Regards Gauri