pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index problem
13+ messages / 7 participants
[nested] [flat]

* Index problem
@ 2003-09-24 10:09 Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2003-09-24 17:03 ` Re: Index problem Tomasz Myrta <jasiek@klaster.net>
  2003-09-24 17:35 ` Re: Index problem Matt Clark <matt@ymogen.net>
  0 siblings, 2 replies; 13+ messages in thread

From: Rigmor Ukuhe @ 2003-09-24 10:09 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have a table containing columns:

  "END_DATE" timestamptz NOT NULL
  "REO_ID" int4 NOT NULL

and i am indexed "REO_ID" coulumn.
I have a query:

select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN
('112851'  ,'112859'  ,'112871'  ,'112883'  ,'112891'  ,'112904'  ,'112915'
,'112924'  ,'112939'  ,'112947'  ,'112960'  ,'112984'  ,'112999'  ,'113013'
,'113032'  ,'113059'  ,'113067'  ,'113084'  ,'113096'  ,'113103'  ,'113110'
,'113117'  ,'113125'  ,'113132'  ,'113139'  ,'113146'  ,'113153'  ,'113160'
,'113167'  ,'113174'  ,'113181'  ,'113188'  ,'113195'  ,'113204'  ,'113268'
,'113279'  ,'113294'  ,'113302'  ,'113317'  ,'113340'  ,'113358'  ,'113385'
,'113404'  ,'113412'  ,'113419'  ,'113429'  ,'113436'  ,'113443'  ,'113571'
,'113636'  ,'113649'  ,'113689'  ,'113705'  ,'113744'  ,'113755'  ,'113724'
,'113737' ,'113812'  ,'113828'  ,'113762'  ,'113842'  ,'113869'  ,'113925'
,'113976'  ,'114035'  ,'114044'  ,'114057'  ,'114070'  ,'114084'  ,'114094'
,'114119' )

and it is _not_ using that index

But following query (notice there are less id-s in WHERE clause, but rest is
same)

select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where  "REO_ID" IN
('112851'  ,'112859'  ,'112871'  ,'112883'  ,'112891'  ,'112904'  ,'112915'
,'112924'  ,'112939'  ,'112947'  ,'112960'  ,'112984'  ,'112999'  ,'113013'
,'113032'  ,'113059'  ,'113067'  ,'113084'  ,'113096'  ,'113103'  ,'113110'
,'113117'  ,'113125'  ,'113132'  ,'113139'  ,'113146'  ,'113153'  ,'113160'
,'113167'  ,'113174'  ,'113181'  ,'113188'  ,'113195'  ,'113204'  ,'113268'
,'113279'  ,'113294'  ,'113302'  ,'113317'  ,'113340'  ,'113358'  ,'113385'
,'113404'  ,'113412'  ,'113419'  ,'113429'  ,'113436'  ,'113443'  ,'113571'
,'113636'  ,'113649'  ,'113689'  ,'113705'  ,'113744'  ,'113755'  ,'113724'
,'113737' )

will _is_ using index:

Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS  (cost=0.00..394.06
rows=102 width=12)

What causes this behaviour? is there any workaround? Suggestions?

best,
Rigmor Ukuhe
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 01.09.2003




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index problem
  2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
@ 2003-09-24 17:03 ` Tomasz Myrta <jasiek@klaster.net>
  1 sibling, 0 replies; 13+ messages in thread

From: Tomasz Myrta @ 2003-09-24 17:03 UTC (permalink / raw)
  To: rigmor.ukuhe@finestmedia.com; +Cc: pgsql-performance

> Hi,
> 
> I have a table containing columns:
> 
>   "END_DATE" timestamptz NOT NULL
>   "REO_ID" int4 NOT NULL
> 
> and i am indexed "REO_ID" coulumn.
> I have a query:
> 
> select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where "REO_ID" IN
> ('112851'  ,'112859'  ,'112871'  ,'112883'  ,'112891'  ,'112904'  ,'112915'
> ,'112924'  ,'112939'  ,'112947'  ,'112960'  ,'112984'  ,'112999'  ,'113013'
> ,'113032'  ,'113059'  ,'113067'  ,'113084'  ,'113096'  ,'113103'  ,'113110'
> ,'113117'  ,'113125'  ,'113132'  ,'113139'  ,'113146'  ,'113153'  ,'113160'
> ,'113167'  ,'113174'  ,'113181'  ,'113188'  ,'113195'  ,'113204'  ,'113268'
> ,'113279'  ,'113294'  ,'113302'  ,'113317'  ,'113340'  ,'113358'  ,'113385'
> ,'113404'  ,'113412'  ,'113419'  ,'113429'  ,'113436'  ,'113443'  ,'113571'
> ,'113636'  ,'113649'  ,'113689'  ,'113705'  ,'113744'  ,'113755'  ,'113724'
> ,'113737' ,'113812'  ,'113828'  ,'113762'  ,'113842'  ,'113869'  ,'113925'
> ,'113976'  ,'114035'  ,'114044'  ,'114057'  ,'114070'  ,'114084'  ,'114094'
> ,'114119' )
> 
> and it is _not_ using that index
> 
> But following query (notice there are less id-s in WHERE clause, but rest is
> same)
> 
> select "REO_ID", "END_DATE" from "PRIORITY_STATISTICS" where  "REO_ID" IN
> ('112851'  ,'112859'  ,'112871'  ,'112883'  ,'112891'  ,'112904'  ,'112915'
> ,'112924'  ,'112939'  ,'112947'  ,'112960'  ,'112984'  ,'112999'  ,'113013'
> ,'113032'  ,'113059'  ,'113067'  ,'113084'  ,'113096'  ,'113103'  ,'113110'
> ,'113117'  ,'113125'  ,'113132'  ,'113139'  ,'113146'  ,'113153'  ,'113160'
> ,'113167'  ,'113174'  ,'113181'  ,'113188'  ,'113195'  ,'113204'  ,'113268'
> ,'113279'  ,'113294'  ,'113302'  ,'113317'  ,'113340'  ,'113358'  ,'113385'
> ,'113404'  ,'113412'  ,'113419'  ,'113429'  ,'113436'  ,'113443'  ,'113571'
> ,'113636'  ,'113649'  ,'113689'  ,'113705'  ,'113744'  ,'113755'  ,'113724'
> ,'113737' )
> 
> will _is_ using index:

Why not. It's just because the second query is more selective. Probably 
you don't have too many rows in your table and Postgres thinks it's 
better (faster) to use sequential scan than index one.

Regards,
Tomasz Myrta




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index problem
  2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
@ 2003-09-24 17:35 ` Matt Clark <matt@ymogen.net>
  2003-09-25 10:22   ` Re: Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Matt Clark @ 2003-09-24 17:35 UTC (permalink / raw)
  To: rigmor.ukuhe@finestmedia.com; pgsql-performance


> What causes this behaviour? is there any workaround? Suggestions?
>

How many rows are there in the table, and can you post the 'explain analyze' for both queries after doing a 'vacuum verbose analyze
[tablename]'?

Cheers

Matt





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index problem
  2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2003-09-24 17:35 ` Re: Index problem Matt Clark <matt@ymogen.net>
@ 2003-09-25 10:22   ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2003-09-25 12:13     ` Re: Index problem Matt Clark <matt@ymogen.net>
  2003-09-25 13:38     ` Re: Index problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 13+ messages in thread

From: Rigmor Ukuhe @ 2003-09-25 10:22 UTC (permalink / raw)
  To: Matt Clark <matt@ymogen.net>; pgsql-performance

> > What causes this behaviour? is there any workaround? Suggestions?
> >
>
> How many rows are there in the table, and can you post the
> 'explain analyze' for both queries after doing a 'vacuum verbose analyze
> [tablename]'?

There are about 2500 rows in that table.

1st query explain analyze: Seq Scan on PRIORITY_STATISTICS
(cost=0.00..491.44 rows=127 width=12) (actual time=98.58..98.58 rows=0
loops=1)
Total runtime: 98.74 msec

2nd query explain analyze: NOTICE:  QUERY PLAN:

Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS  (cost=0.00..394.06
rows=102 width=12) (actual time=20.93..20.93 rows=0 loops=1)
Total runtime: 21.59 msec

Any help?

Rigmor


>
> Cheers
>
> Matt
>
>
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.515 / Virus Database: 313 - Release Date: 01.09.2003
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 01.09.2003




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index problem
  2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2003-09-24 17:35 ` Re: Index problem Matt Clark <matt@ymogen.net>
  2003-09-25 10:22   ` Re: Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
@ 2003-09-25 12:13     ` Matt Clark <matt@ymogen.net>
  1 sibling, 0 replies; 13+ messages in thread

From: Matt Clark @ 2003-09-25 12:13 UTC (permalink / raw)
  To: rigmor.ukuhe@finestmedia.com; pgsql-performance

> There are about 2500 rows in that table.
>
> 1st query explain analyze: Seq Scan on PRIORITY_STATISTICS
> (cost=0.00..491.44 rows=127 width=12) (actual time=98.58..98.58 rows=0
> loops=1)
> Total runtime: 98.74 msec
>
> 2nd query explain analyze: NOTICE:  QUERY PLAN:
>
> Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id,
[snip]
> PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS  (cost=0.00..394.06
> rows=102 width=12) (actual time=20.93..20.93 rows=0 loops=1)
> Total runtime: 21.59 msec

With only 2500 rows the planner could be deciding that it's going to have to read every disk block to do an index scan anyway, so it
might as well do a sequential scan.  If the pages are in fact in the kernel cache then the compute time will dominate, not the IO
time, so it ends up looking like a bad plan, but it's probably not really such a bad plan...

Is your effective_cache_size set to something sensibly large?

You could also try decreasing cpu_index_tuple_cost and cpu_tuple_cost.  These will affect all your queries though, so what you gain
on one might be lost on another.

Matt





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index problem
  2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2003-09-24 17:35 ` Re: Index problem Matt Clark <matt@ymogen.net>
  2003-09-25 10:22   ` Re: Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
@ 2003-09-25 13:38     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 13+ messages in thread

From: Tom Lane @ 2003-09-25 13:38 UTC (permalink / raw)
  To: rigmor.ukuhe@finestmedia.com; +Cc: Matt Clark <matt@ymogen.net>; pgsql-performance

"Rigmor Ukuhe" <rigmor.ukuhe@finestmedia.com> writes:
>>> What causes this behaviour? is there any workaround? Suggestions?

At some point the planner is going to decide that one seqscan is cheaper
than repeated indexscans.  At some point it'll be right ... but in this
case it seems its relative cost estimates are off a bit.  You might try
reducing random_page_cost to bring them more into line with reality.
(But keep in mind that the reality you are measuring appears to be
small-table-already-fully-cached reality.  On a large table you might
find that small random_page_cost isn't such a hot idea after all.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Index Problem?
@ 2004-04-16 16:41 Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  2004-04-17 12:25 ` Re: Index Problem? Jochem van Dieten <jochemd@oli.tudelft.nl>
  0 siblings, 2 replies; 13+ messages in thread

From: Ron St-Pierre @ 2004-04-16 16:41 UTC (permalink / raw)
  To: pgsql-performance

I am using postgres 7.4.1 and have a problem with a plpgsql function. 
When I run the function on the production server it takes approx 33 
minutes to run. I dumped the DB and copied it to a similarly configured 
box and ran the function and it ran in about 10 minutes. Can anyone 
offer advice on tuning the function or my database? Here are the 
lengthy, gory details.

F u n c t i o n
It updates seven columns of a table 1 to 4 times daily. Current data = 
42,000 rows, new data = 30,000 rows.

    CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate 
INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum 
NUMERIC);

    CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF 
employeeType AS '
        DECLARE
            rec     RECORD;
        BEGIN
            FOR rec IN SELECT empID, updateDate, bDate, val1, val2 , 
val3, val4, favNum FROM newData LOOP
                RETURN NEXT rec;
                UPDATE currentData SET val1=rec.val1, val2=rec.val2, 
val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate
                WHERE empID=rec.empID;
            END LOOP;
            RETURN;
        END;
    ' LANGUAGE 'plpgsql';

The emp table has 60 columns, all indexed, about two-thirds are numeric, 
but they are not affected by this update. The other 50+ columns are 
updated in the middle of the night and the amount of time that update 
takes isn't a concern.

Late last night I dumped the table, dropped it and re-created it from 
the dump (on the production server - when no one was looking). When I 
re-ran the function it took almost 11 minutes, which was pretty much in 
line with my results from the dev server.

D e t a i l s
v 7.4.1
Debian stable
1 GB ram
shared_buffers = 2048
sort_mem = 1024
SHMMAX 360000000 (360,000,000)
VACUUM FULL ANALYZE is run every night, and I ran it yesterday between 
running the function and it made no difference in running time.
top shows the postmaster using minimal cpu (0-40%) and miniscule memory. 
vmstat shows a fair amount of IO (bo=1000->4000).

Yesterday on the dev server we upgraded to the 2.6 kernel and 
unfortunately only noticed a small increase in update time (about one 
minute).
So does anyone have any suggestions for me on speeding this up? Is it 
the index?  The function is run daily during the mid afternoon to early 
evening and really drags the performance of the server down (it also 
hosts a web site).

Thanks
Ron





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
@ 2004-04-16 17:01 ` Josh Berkus <josh@agliodbs.com>
  2004-04-16 17:55   ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Josh Berkus @ 2004-04-16 17:01 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; pgsql-performance

Ron,

> The emp table has 60 columns, all indexed, about two-thirds are numeric, 
> but they are not affected by this update. The other 50+ columns are 
> updated in the middle of the night and the amount of time that update 
> takes isn't a concern.

Well, I'd say that you have an application design problem, but that's not what 
you asked for help with ;-)

> Late last night I dumped the table, dropped it and re-created it from 
> the dump (on the production server - when no one was looking). When I 
> re-ran the function it took almost 11 minutes, which was pretty much in 
> line with my results from the dev server.

Sounds like you need to run a REINDEX on the table -- and after that, 
dramatically increase your max_fsm_pages, and run lazy VACUUM immediately 
after the batch update to clean up.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
@ 2004-04-16 17:55   ` Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 19:10     ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Ron St-Pierre @ 2004-04-16 17:55 UTC (permalink / raw)
  To: pgsql-performance

Josh Berkus wrote:

>Ron,
>
>  
>
>>The emp table has 60 columns, all indexed, about two-thirds are numeric, 
>>but they are not affected by this update. The other 50+ columns are 
>>updated in the middle of the night and the amount of time that update 
>>takes isn't a concern.
>>    
>>
>
>Well, I'd say that you have an application design problem, but that's not what 
>you asked for help with ;-)
>  
>
Yeah I agree but I'm not allowed to remove those indexes.

>  
>
>>Late last night I dumped the table, dropped it and re-created it from 
>>the dump (on the production server - when no one was looking). When I 
>>re-ran the function it took almost 11 minutes, which was pretty much in 
>>line with my results from the dev server.
>>    
>>
>
>Sounds like you need to run a REINDEX on the table -- and after that, 
>dramatically increase your max_fsm_pages, and run lazy VACUUM immediately 
>after the batch update to clean up.
>
>  
>
On my dev server I increased max_fsm_pages from the default of 20000 to 
40000, increased checkpoint_segments from 3 to 5, and the function ran 
in about 6-7 minutes which is a nice increase. According to the docs 
"Annotated postgresql.conf and Global User Configuration (GUC) Guide" on 
varlena I'll have to re-start postgres for the changes to take effect 
there (correct?). Also the docs on Varlena show the max_fsm_pages 
default to be 10,000 but my default was 20,000, looks like that needs 
updating.

Thanks for your help Josh, I'll see after the weekend what the impact 
the changes will have on the production server.

Ron




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  2004-04-16 17:55   ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
@ 2004-04-16 19:10     ` Josh Berkus <josh@agliodbs.com>
  2004-04-16 20:05       ` Re: Index Problem? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Josh Berkus @ 2004-04-16 19:10 UTC (permalink / raw)
  To: Ron St-Pierre <rstpierre@syscor.com>; pgsql-performance

Ron,

> Yeah I agree but I'm not allowed to remove those indexes.

It's not the indexes I'm talking about, it's the table.

> On my dev server I increased max_fsm_pages from the default of 20000 to 
> 40000, 

A better way to set this would be to run VACUUM VERBOSE ANALYZE right after 
doing one of your update batches, and see how many dead pages are being 
reclaimed, and then set max_fsm_pages to that # + 50% (or more).

increased checkpoint_segments from 3 to 5, and the function ran 
> in about 6-7 minutes which is a nice increase. According to the docs 
> "Annotated postgresql.conf and Global User Configuration (GUC) Guide" on 
> varlena I'll have to re-start postgres for the changes to take effect 
> there (correct?).

Correct.

> Also the docs on Varlena show the max_fsm_pages 
> default to be 10,000 but my default was 20,000, looks like that needs 
> updating.

I don't think the default has been changed.   Anyone?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  2004-04-16 17:55   ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 19:10     ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
@ 2004-04-16 20:05       ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-16 21:54         ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2004-04-16 20:05 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Ron St-Pierre <rstpierre@syscor.com>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> A better way to set this would be to run VACUUM VERBOSE ANALYZE right after 
> doing one of your update batches, and see how many dead pages are being 
> reclaimed, and then set max_fsm_pages to that # + 50% (or more).

Actually, since he's running 7.4, there's an even better way.  Do a
"VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you
ANALYZE or not).  At the end of the very voluminous output, you'll see
something like

INFO:  free space map: 240 relations, 490 pages stored; 4080 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.

Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to
exactly cover the present freespace needs of my system.  I concur with
the suggestion to bump that up a good deal, of course, but that gives
you a real number to start from.

The DETAIL part of the message shows my current settings (which are the
defaults) and what the FSM is costing me in shared memory space.

If you have multiple active databases, the best approach to getting
these numbers is to VACUUM in each one, adding VERBOSE when you do the
last one.  The FSM report is cluster-wide but you want to be sure the
underlying info is up to date for all databases.

>> Also the docs on Varlena show the max_fsm_pages 
>> default to be 10,000 but my default was 20,000, looks like that needs 
>> updating.

> I don't think the default has been changed.   Anyone?

Yes, I kicked it up for 7.4 because FSM covers indexes too now.
Both the 7.3 and 7.4 defaults are pretty arbitrary of course...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  2004-04-16 17:55   ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
  2004-04-16 19:10     ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
  2004-04-16 20:05       ` Re: Index Problem? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-16 21:54         ` Ron St-Pierre <rstpierre@syscor.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Ron St-Pierre @ 2004-04-16 21:54 UTC (permalink / raw)
  To: pgsql-performance

Tom Lane wrote:

>Josh Berkus <josh@agliodbs.com> writes:
>  
>
>>A better way to set this would be to run VACUUM VERBOSE ANALYZE right after 
>>doing one of your update batches, and see how many dead pages are being 
>>reclaimed, and then set max_fsm_pages to that # + 50% (or more).
>>    
>>
>
>Actually, since he's running 7.4, there's an even better way.  Do a
>"VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you
>ANALYZE or not).  At the end of the very voluminous output, you'll see
>something like
>
>INFO:  free space map: 240 relations, 490 pages stored; 4080 total pages needed
>DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.
>
>Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to
>exactly cover the present freespace needs of my system.  I concur with
>the suggestion to bump that up a good deal, of course, but that gives
>you a real number to start from.
>
>The DETAIL part of the message shows my current settings (which are the
>defaults) and what the FSM is costing me in shared memory space.
>
>  
>
Okay, after running the function VACUUM VERBOSE is telling me:
 INFO:  free space map: 136 relations, 25014 pages stored; 22608 total 
pages needed
 DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB 
shared memory.

My max_fsm_pages was set to 20,000 and I reset it to 40,000 on the dev 
server and the function ran about 20-30% faster, so I'll try the same on 
the production server. Thanks for the analysis of the VACUUM info.

Ron




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Index Problem?
  2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
@ 2004-04-17 12:25 ` Jochem van Dieten <jochemd@oli.tudelft.nl>
  1 sibling, 0 replies; 13+ messages in thread

From: Jochem van Dieten @ 2004-04-17 12:25 UTC (permalink / raw)
  To: rstpierre@syscor.com; +Cc: pgsql-performance

Ron St-Pierre wrote:
> I am using postgres 7.4.1 and have a problem with a plpgsql function. 
> When I run the function on the production server it takes approx 33 
> minutes to run. I dumped the DB and copied it to a similarly configured 
> box and ran the function and it ran in about 10 minutes. Can anyone 
> offer advice on tuning the function or my database? Here are the 
> lengthy, gory details.
> 
> F u n c t i o n
> It updates seven columns of a table 1 to 4 times daily. Current data = 
> 42,000 rows, new data = 30,000 rows.
> 
>    CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate 
> INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum 
> NUMERIC);
> 
>    CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF 
> employeeType AS '
>        DECLARE
>            rec     RECORD;
>        BEGIN
>            FOR rec IN SELECT empID, updateDate, bDate, val1, val2, val3, val4, favNum FROM newData LOOP
>                RETURN NEXT rec;
>                UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate
>                WHERE empID=rec.empID;
>            END LOOP;
>            RETURN;
>        END;
>    ' LANGUAGE 'plpgsql';

Can't you handle this with a simple update query?

UPDATE
	currentData
SET
	val1 = newData.val1,
	val2 = newData.val2,
	val3 = newData.val3,
	val4 = newData.val4,
	favNum = newData.favNum,
	updateDate = newData.updateDate
FROM
	newData
WHERE
	newDate.empID = currentData.empID

Jochem

-- 
I don't get it
immigrants don't work
and steal our jobs
     - Loesje





^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2004-04-17 12:25 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-24 10:09 Index problem Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
2003-09-24 17:03 ` Tomasz Myrta <jasiek@klaster.net>
2003-09-24 17:35 ` Matt Clark <matt@ymogen.net>
2003-09-25 10:22   ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
2003-09-25 12:13     ` Matt Clark <matt@ymogen.net>
2003-09-25 13:38     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-16 16:41 Index Problem? Ron St-Pierre <rstpierre@syscor.com>
2004-04-16 17:01 ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
2004-04-16 17:55   ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
2004-04-16 19:10     ` Re: Index Problem? Josh Berkus <josh@agliodbs.com>
2004-04-16 20:05       ` Re: Index Problem? Tom Lane <tgl@sss.pgh.pa.us>
2004-04-16 21:54         ` Re: Index Problem? Ron St-Pierre <rstpierre@syscor.com>
2004-04-17 12:25 ` Re: Index Problem? Jochem van Dieten <jochemd@oli.tudelft.nl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox