pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex 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