Re: [PERFORM] LIKE query running slow
Garrett Bladow wrote: Recently we upgraded the RAM in our server. After the install a LIKE query that used to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, ANALYZE and Re-indexing. Any thoughts on what might have happened? What all tuning you have done? Have you set effective cache size to take care of additional RAM. Just check out. Shridhar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] LIKE query running slow
On Tue, 23 Sep 2003, Garrett Bladow wrote: > Recently we upgraded the RAM in our server. After the install a LIKE query that used > to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, > ANALYZE and Re-indexing. > > Any thoughts on what might have happened? > Did you reload the db? If you did perhaps you didn't use the "C" locale? That can cause a huge slowdown. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] LIKE query running slow
On Tue, 23 Sep 2003, Garrett Bladow wrote: > Recently we upgraded the RAM in our server. After the install a LIKE > query that used to take 5 seconds now takes 5 minutes. We have tried the > usual suspects, VACUUM, ANALYZE and Re-indexing. If you mean that you reinstalled postgresql then it's probably because you before run the database with the "C" locale but now you run it with something else. If all you did was to install the extra memory then I don't see how that can affect it at all (especially so if you have not altered postgresql.conf to make use of more memory). -- /Dennis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Performance issue
Hello, I have been trying to get my Postgres database to do faster inserts. The environment is basically a single user situation. The part that I would like to speed up is when a User copys a Project. A Project consists of a number of Rooms(say 60). Each room contains a number of items. A project will contain say 20,000 records. Anyway the copying process gets slower and slower, as more projects are added to the database. My statistics(Athlon 1.8Ghz) 20,000 itemsTakes on average 0.078seconds/room 385,000 items Takes on average .11seconds/room 690,000 items takes on average .270seconds/room 1,028,000 items Takes on average .475seconds/room As can be seen the time taken to process each room increases. A commit occurs when a room has been copied. The hard drive is not being driven very hard. The hard drive light only flashes about twice a second when there are a million records in the database. I thought that the problem could have been my plpgsql procedure because I assume the code is interpreted. However I have just rewriten the code using straight sql(with some temp fields), and the times turn out to be almost exactly the same as the plpgsql version. The read speed for the Application is fine. The sql planner seems to be doing a good job. There has been only one problem that I have found with one huge select, which was fixed by a cross join. I am running Red hat 8. Some of my conf entries that I have changed follow shared_buffers = 3700 effective_cache_size = 4000 sort_mem = 32168 Are the increasing times reasonable? The times themselves might look slow, but thats because there are a number of tables involved in a Copy I can increase the shared buffer sizes above 32M, but would this really help? TIA peter Mcgregor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to make n_distinct more accurate.
The performance list seemed to be off-line for a while, so I posed the same question on the admin list and Tom Lane has been helping in that forum. -Nick > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Nick > Fankhauser > Sent: Monday, September 22, 2003 3:42 PM > To: [EMAIL PROTECTED] Org > Subject: [PERFORM] How to make n_distinct more accurate. > > > Hi- > > I have a table- called "event" with a field event_date_time that > is indexed. > There are 1,700,000 rows in the table and 92,000 distinct values of > event_date_time with anywhere from 1 to 400 rows sharing the same > value. (I > did a count grouped by event_date_time & scanned it to get this info.) > > When I look at the pg_stats on this table, I always see 15,000 or lower in > the n_distinct column for event_date_time. (I re-ran analyze > several times & > then checked pg_stats to see if the numbers varied significantly.) > > Since this is off by about a factor of 6, I think the planner is > missing the > chance to use this table as the "driver" in a complex query plan that I'm > trying to optimize. > > So the question is- how can I get a better estimate of n_distinct from > analyze? > > If I alter the stats target as high as it will go, I get closer, but it > still shows the index to be about 1/2 as selective as it actually is: > > alpha=# alter table event alter column event_date_time set > statistics 1000; > ALTER TABLE > alpha=# analyze event; > ANALYZE > alpha=# select n_distinct from pg_stats where tablename='event' and > attname='event_date_time'; > n_distinct > > 51741 > (1 row) > > This number seems to be consistently around 51,000 if I re-run > analyze a few > times. > > I guess my question is two-part: > > (1)Is there any tweak to make this estimate work better? > > (2)Since I'm getting numbers that are consistent but way off, is > there a bug > here? > > (2-1/2) Or alternately, am I totally missing what n-distinct is > supposed to > denote? > > Thanks! >-Nick > > - > Nick Fankhauser > > [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Index problem
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] LIKE query running slow
On Tue, 2003-09-23 at 20:24, Garrett Bladow wrote: > Recently we upgraded the RAM in our server. After the install a LIKE query that used > to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, > ANALYZE and Re-indexing. > > Any thoughts on what might have happened? What settings did you change at that time? Care to share an EXPLAIN ANALYZE with us? signature.asc Description: This is a digitally signed message part
Re: [PERFORM] LIKE query running slow
Garrett, > Recently we upgraded the RAM in our server. After the install a LIKE query that used to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, ANALYZE and Re-indexing. > > Any thoughts on what might have happened? Bad RAM? Have you tested it? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] restore time: sort_mem vs. checkpoing_segments
On Tue, 23 Sep 2003, Bruce Momjian wrote: > With the new warning about too-frequent checkpoints, people have actual > feedback to encourage them to increase checkpoint_segments. One issue > is that it is likely to recommend increasing checkpoint_segments during > restore, even if there is no value to it being large during normal > server operation. Should that be decumented? One could have a variable that turns off that warning, and have pg_dump insert a statement to turn it off. That is, if one never want these warnings from a restore (from a new dump). In any case, documentation is good and still needed. -- /Dennis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] LIKE query running slow
On Tue, 23 Sep 2003, Josh Berkus wrote: > Garrett, > > > Recently we upgraded the RAM in our server. After the install a LIKE query > that used to take 5 seconds now takes 5 minutes. We have tried the usual > suspects, VACUUM, ANALYZE and Re-indexing. > > > > Any thoughts on what might have happened? > > Bad RAM? Have you tested it? RAM was tested and is good. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Performance issue
Peter, One possibility is to drop all the indexes, do the insert and re-add the indexes. The more indexes that exist and the more rows that exist, the more costly the insert. Regards, Joseph At 05:48 PM 9/24/2003 +1200, peter wrote: Hello, I have been trying to get my Postgres database to do faster inserts. The environment is basically a single user situation. The part that I would like to speed up is when a User copys a Project. A Project consists of a number of Rooms(say 60). Each room contains a number of items. A project will contain say 20,000 records. Anyway the copying process gets slower and slower, as more projects are added to the database. My statistics(Athlon 1.8Ghz) 20,000 itemsTakes on average 0.078seconds/room 385,000 items Takes on average .11seconds/room 690,000 items takes on average .270seconds/room 1,028,000 items Takes on average .475seconds/room As can be seen the time taken to process each room increases. A commit occurs when a room has been copied. The hard drive is not being driven very hard. The hard drive light only flashes about twice a second when there are a million records in the database. I thought that the problem could have been my plpgsql procedure because I assume the code is interpreted. However I have just rewriten the code using straight sql(with some temp fields), and the times turn out to be almost exactly the same as the plpgsql version. The read speed for the Application is fine. The sql planner seems to be doing a good job. There has been only one problem that I have found with one huge select, which was fixed by a cross join. I am running Red hat 8. Some of my conf entries that I have changed follow shared_buffers = 3700 effective_cache_size = 4000 sort_mem = 32168 Are the increasing times reasonable? The times themselves might look slow, but thats because there are a number of tables involved in a Copy I can increase the shared buffer sizes above 32M, but would this really help? TIA peter Mcgregor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Index problem
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Performance issue
get rid of any unnecessary indexes? i've found that droping indexes and re-creating them isn't usually worth the effort mount the disk with the noatime option which saves you the time involved in updating the last access time on files make sure you're doing all the inserts in one transaction.. wrapping a bunch of INSERTS in BEGIN & COMMIT speeds them up loads. > At 05:48 PM 9/24/2003 +1200, peter wrote: > >Hello, > > > >I have been trying to get my Postgres database to do faster inserts. > > > >The environment is basically a single user situation. > > > >The part that I would like to speed up is when a User copys a Project. > >A Project consists of a number of Rooms(say 60). Each room contains a > >number of items. > >A project will contain say 20,000 records. > > > >Anyway the copying process gets slower and slower, as more projects are > >added to the database. > > > >My statistics(Athlon 1.8Ghz) > > > >20,000 itemsTakes on average 0.078seconds/room > >385,000 items Takes on average .11seconds/room > >690,000 items takes on average .270seconds/room > >1,028,000 items Takes on average .475seconds/room > > > >As can be seen the time taken to process each room increases. A commit > >occurs when a room has been copied. > >The hard drive is not being driven very hard. The hard drive light only > >flashes about twice a second when there are a million records in the > > database. > > > >I thought that the problem could have been my plpgsql procedure because I > >assume the code is interpreted. > >However I have just rewriten the code using straight sql(with some temp > >fields), > >and the times turn out to be almost exactly the same as the plpgsql > > version. > > > >The read speed for the Application is fine. The sql planner seems to be > >doing a good job. There has been only one problem > >that I have found with one huge select, which was fixed by a cross join. > > > > I am running Red hat 8. Some of my conf entries that I have changed > > follow shared_buffers = 3700 > >effective_cache_size = 4000 > >sort_mem = 32168 > > > >Are the increasing times reasonable? > >The times themselves might look slow, but thats because there are a number > >of tables involved in a Copy > > > >I can increase the shared buffer sizes above 32M, but would this really > > help? > > > >TIA > > > >peter Mcgregor > > > > > >---(end of broadcast)--- > >TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Index problem
> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Performance issue
> My statistics(Athlon 1.8Ghz) > > 20,000 itemsTakes on average 0.078seconds/room > 385,000 items Takes on average .11seconds/room > 690,000 items takes on average .270seconds/room > 1,028,000 items Takes on average .475seconds/room [snip] > I am running Red hat 8. Some of my conf entries that I have changed > follow > shared_buffers = 3700 > effective_cache_size = 4000 > sort_mem = 32168 Have you twiddled with your wal_buffers or checkpoint_segments? Might be something to look at. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Performance issue
> 20,000 itemsTakes on average 0.078seconds/room > 385,000 items Takes on average .11seconds/room > 690,000 items takes on average .270seconds/room > 1,028,000 items Takes on average .475seconds/room > > As can be seen the time taken to process each room increases. A commit > occurs when a room has been copied. It probably isn't the insert that is getting slower, but a select. Foreign keys to growing tables will exhibit this behaviour. Since the time is doubling with the number of items, you might want to check for a SELECT working with a sequential scan rather than an index scan. signature.asc Description: This is a digitally signed message part
[PERFORM] upping checkpoints on production server
All this talk of checkpoints got me wondering if I have them set at an
optimum level on my production servers. I noticed the following in the
docs:
"There will be at least one 16 MB segment file, and will normally not
be more than 2 * checkpoint_segments + 1 files. You can use this to
estimate space requirements for WAL. Ordinarily, when old log segment
files are no longer needed, they are recycled (renamed to become the
next segments in the numbered sequence). If, due to a short-term peak of
log output rate, there are more than 2 * checkpoint_segments + 1 segment
files, the unneeded segment files will be deleted instead of recycled
until the system gets back under this limit."
In .conf file I have default checkpoints set to 3, but I noticed that in
my pg_xlog directory I always seem to have at least 8 log files. Since
this is more than the suggested 7, I'm wondering if this means I ought
to bump my checkpoint segments up to 4? I don't really want to bump it
up unnecessarily as quick recover time is important on this box, however
if i would get an overall performance boost it seems like it would be
worth it, and given that I seem to be using more than the default number
anyways... I've always treated wal logs as self maintaining, am I over
analyzing this?
Another thought popped into my head, is it just coincidence that I
always seem to have 8 files and that wal_buffers defaults to 8? Seems
like it's not but I love a good conspiracy theory.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] upping checkpoints on production server
Robert Treat <[EMAIL PROTECTED]> writes: > In .conf file I have default checkpoints set to 3, but I noticed that in > my pg_xlog directory I always seem to have at least 8 log files. Since > this is more than the suggested 7, I'm wondering if this means I ought > to bump my checkpoint segments up to 4? Hm. What is the typical delta in the mod times of the log files? It sounds like you are in a regime where checkpoints are always triggered by checkpoint_segments and never by checkpoint_timeout, in which case increasing the former might be a good idea. Or decrease the latter, but that could put a drag on performance. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] osdl-dbt3 run results - puzzled by the execution
On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <[EMAIL PROTECTED]> wrote: >I posted more results as you requested: Unfortunately they only confirm what I suspected earlier: >> 2) -> Index Scan using i_ps_suppkey on partsupp >> (cost=0.00..323.16 rows=80 width=34) >> (actual time=0.16..2.98 rows=80 loops=380) >> ctr=108.44 >> the planner does not >> account for additional index scans hitting pages in the cache that >> have been brought in by preceding scans. This is a known problem PF1 = estimated number of page fetches for one loop ~ 320 L = estimated number of loops ~ 400 P = number of pages in relation ~ 21000 Cutting down the number of heap page fetches if PF1 * L > P and P < effective_cache_size seems like an obvious improvement, but I was not able to figure out where to make this change. Maybe it belongs into costsize.c near run_cost += outer_path_rows * (inner_path->total_cost - inner_path->startup_cost) * joininfactor; in cost_nestloop() or it should be pushed into the index cost estimation functions. Hackers? For now you have to keep lying about effective_cache_size to make the planner overestimate merge joins to compensate for the planner's overestimation of nested loops. Sorry for having no better answer. Servus Manfred ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PERFORM] osdl-dbt3 run results - puzzled by the execution
Manfred Koizar <[EMAIL PROTECTED]> writes: > Cutting down the number of heap page fetches if PF1 * L > P and P < > effective_cache_size seems like an obvious improvement, but I was not > able to figure out where to make this change. Maybe it belongs into > costsize.c near > run_cost += outer_path_rows * > (inner_path->total_cost - inner_path->startup_cost) * > joininfactor; I've been intending for some time to try to restructure the cost estimator so that repeated indexscans can be costed more accurately. Within the context of the heap-fetch-estimating algorithm, I think the entire execution of a nestloop-with-inner-index-scan could probably be treated as a single scan. I'm not sure how we adjust the estimates for the index-access part, though clearly those are too high as well. This doesn't seem to be a localized change unfortunately. Certainly costsize.c can't do it alone. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
