Re: [PERFORM] Databases vs Schemas
On Fri, Oct 9, 2009 at 11:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Scott Carey sc...@richrelevance.com writes:
I've got 200,000 tables in one db (8.4), and some tools barely work. The
system catalogs get inefficient when large and psql especially has
trouble.
Tab completion takes forever, even if I make a schema s with one table
in
it and type s. and try and tab complete -- its as if its scanning all
without a schema qualifier or using an index.
The tab-completion queries have never been vetted for performance
particularly :-(
Just out of curiosity, how much does this help?
alter function pg_table_is_visible(oid) cost 10;
(You'll need to do it as superuser --- if it makes things worse, just
set the cost back to 1.)
Sometimes it does not match
valid tables at all, and sometimes regex matching fails too ('\dt
schema.*_*_*' intermittently flakes out if it returns a lot of matches).
There are some arbitrary LIMIT 1000 clauses in those queries, which
probably explains this ... but taking them out would likely cause
libreadline to get indigestion ...
regards, tom lane
We ran into this exact situation with a pg 8.3 database and a very large
number of tables. psql would wait for 20 to 30 seconds if the user was
unlucky enough to hit the tab key. After doing some research with query
logging, explain analyze and some trial and error, we came to the same
conclusion. Altering the cost for the pg_table_is_visible function to 10
fixed our performance problem immediately. It appears that when the cost
was set to 1, that the query optimizer first ran the function over the
entire pg_class table. By increasing the cost, it now only runs the
function over the rows returned by the other items in the where clause.
-chris
Re: [PERFORM] Incorrect estimates on correlated filters
On Wed, Aug 13, 2008 at 10:59 AM, Decibel! [EMAIL PROTECTED] wrote: On Aug 12, 2008, at 4:59 PM, Chris Kratz wrote: Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated. So, the estimates come out grossly incorrect (the planner has no way to know they are correlated). 2000:1 for one I'm looking at right now. Generally this doesn't matter, except in complex reporting queries like these when this is the first join of 40 other joins. Because the estimate is wrong at the lowest level, it snowballs up through the rest of the joins causing the query to run very, very slowly. In many of these cases, forcing nested loops off for the duration of the query fixes the problem. But I have a couple that still are painfully slow and shouldn't be. I've been reading through the archives with others having similar problems (including myself a year ago). Am I right in assuming that at this point there is still little we can do in postgres to speed up this kind of query? Right now the planner has no way to know the correlation between different columns in the same table, let alone columns in different tables. So, it just assumes no correlation and returns incorrectly low estimates in cases like these. The only solution I've come up with so far is to materialize portions of the larger query into subqueries with these correlated filters which are indexed and analyzed before joining into the larger query. This would keep the incorrect estimates from snowballing up through the chain of joins. Are there any other solutions to this problem? Well... you could try and convince certain members of the community that we actually do need some kind of a query hint mechanism... ;) I did make a suggestion a few months ago that involved sorting a table on different columns and recording the correlation of other columns. The scheme isn't perfect, but it would help detect cases like a field populated by a sequence and another field that's insert timestamp; those two fields would correlate highly, and you should even be able to correlate the two histograms; that would allow you to infer that most of the insert times for _id's between 100 and 200 will be between 2008-01-01 00:10 and 2008-01-01 00:20, for example. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Thanks for the reply, Yes, I know hints are frowned upon around here. Though, I'd love to have them or something equivalent on this particular query just so the customer can run their important reports. As it is, it's unrunnable. Unfortunately, if I don't think the sorting idea would help in the one case I'm looking at which involves filters on two tables that are joined together. The filters happen to be correlated such that about 95% of the rows from each filtered table are actually returned after the join. Unfortunately, the planner thinks we will get 1 row back. I do have to find a way to make these queries runnable. I'll keep looking. Thanks, -Chris
[PERFORM] Incorrect estimates on correlated filters
Hello All, Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated. So, the estimates come out grossly incorrect (the planner has no way to know they are correlated). 2000:1 for one I'm looking at right now. Generally this doesn't matter, except in complex reporting queries like these when this is the first join of 40 other joins. Because the estimate is wrong at the lowest level, it snowballs up through the rest of the joins causing the query to run very, very slowly. In many of these cases, forcing nested loops off for the duration of the query fixes the problem. But I have a couple that still are painfully slow and shouldn't be. I've been reading through the archives with others having similar problems (including myself a year ago). Am I right in assuming that at this point there is still little we can do in postgres to speed up this kind of query? Right now the planner has no way to know the correlation between different columns in the same table, let alone columns in different tables. So, it just assumes no correlation and returns incorrectly low estimates in cases like these. The only solution I've come up with so far is to materialize portions of the larger query into subqueries with these correlated filters which are indexed and analyzed before joining into the larger query. This would keep the incorrect estimates from snowballing up through the chain of joins. Are there any other solutions to this problem? Thanks, -Chris
[PERFORM] Planner mis-estimation using nested loops followup
A number of weeks ago, I had posted a request for help regarding join estimates in pg 8.2.6. In moderately complex to very complex ad hoc queries in our system, we were consistently having the system massively underestimate the number of rows coming out of join at a low level making these queries very slow and inefficient. At times the mis-estimation was 1000:1. Ie when it should have been 2000 returned rows from a join, the planner assumed 1 or 2 rows. Modifying stats on the join columns up to the max made little difference (y, we analyzed tables in question after each change). Since the planner sees only one row coming out of the low level join, it uses nested loops all the way up chain when it would be more efficient to use another join type. In our informal testing, we found that by disabling nested loops and forcing other join types, we could get fantastic speedups. Those queries that seem to benefit most from this have a lot of sub-queries being built up into a final query set as well as a fair number of joins in the sub-queries. Since these are user created and are then generated via our tools, they can be quite messy at times. After doing this testing, have since added some functionality in our ad hoc reporting tool to allow us to tune individual queries by turning on and off individual join types at runtime. As we hear of slow reports, we've been individually turning off the nested loops on those reports. Almost always, this has increased the performance of the reports, sometimes in a completely amazing fashion (many, many minutes to seconds at times). It of course doesn't help everything and turning off nested loops in general causes overall slowdown in other parts of the system. As this has gone on over the last couple of weeks, it feels like we either have a misconfiguration on the server, or we are tickling a mis-estimation bug in the planner. I'm hoping it's the former. The db server has 8G of memory and raid1 -wal, raid10- data configuration, os is linux 2.6.9, db is 8.2.6. The db is a utf-8 db if that is of any bearing and autovac and bgwriter are on. Nondefault settings of interest from postgresql.conf shared_buffers = 1024MB # min 128kB or max_connections*16kB work_mem = 256MB# min 64kB maintenance_work_mem = 256MB# min 1MB random_page_cost = 1.75 # same scale as above effective_cache_size = 4096MB default_statistics_target = 100 # range 1-1000 If nothing else, perhaps this will help somebody else who has run into the same problem. If explain analyze of a query shows a large mis-estimation of rows returned on a join (estimate=1, actual=2k) causing the planner to choose nested loops instead of another join type, you might try running the query with nested loops set to off and see if that helps w/ performance. Thanks, -Chris
Re: [PERFORM] Planner mis-estimation using nested loops followup
Y, turning nested loops off in specific cases has increased performance greatly. It didn't fix the planner mis-estimation, just the plan it chose. It's certainly not a panacea, but it's something we now try early on when trying to speed up a query that matches these characteristics. -Chris On 3/18/08, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Mar 2008 11:35:08 -0400 Chris Kratz [EMAIL PROTECTED] wrote: Nondefault settings of interest from postgresql.conf shared_buffers = 1024MB # min 128kB or max_connections*16kB work_mem = 256MB # min 64kB maintenance_work_mem = 256MB# min 1MB random_page_cost = 1.75 # same scale as above effective_cache_size = 4096MB default_statistics_target = 100 # range 1-1000 If nothing else, perhaps this will help somebody else who has run into the same problem. If explain analyze of a query shows a large mis-estimation of rows returned on a join (estimate=1, actual=2k) causing the planner to choose nested loops instead of another join type, you might try running the query with nested loops set to off and see if that helps w/ performance. Did you try that? Did it work? Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH3+TlATb/zqfZUUQRAmXUAKCjwidfW0KXjzUM26I4yTx94/wSiQCfaqWU eI9i5yucBH718okW3w2UewQ= =BO3E -END PGP SIGNATURE-
[PERFORM] Ramifications of turning off Nested Loops for slow queries
Hello Everyone, I had posted an issue previously that we've been unable to resolve. An early mis-estimation in one or more subqueries causes the remainder of the query to choose nested loops instead of a more efficient method and runs very slowly (CPU Bound). I don't think there is any way to suggest to the planner it not do what it's doing, so we are starting to think about turning off nested loops entirely. Here is the history so far: http://archives.postgresql.org/pgsql-performance/2008-02/msg00205.php At the suggestion of the list, we upgraded to 8.2.6 and are still experiencing the same problem. I'm now installing 8.3 on my workstation to see if it chooses a better plan, but it will take some time to get it compiled, a db loaded, etc. We have a number of very long running reports that will run in seconds if nested loops are turned off. The other alternative we are exploring is programmatically turning off nested loops just for the problematic reports. But with the speedups we are seeing, others are getting gun shy about having them on at all. So, I've now been asked to ping the list as to whether turning off nested loops system wide is a bad idea, and why or why not. Any other thoughts or suggestions? Thanks, -Chris -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-performance
Re: [PERFORM] Ramifications of turning off Nested Loops for slow queries
On 3/4/08, Kevin Grittner [EMAIL PROTECTED] wrote: On Tue, Mar 4, 2008 at 8:42 AM, in message Any other thoughts or suggestions? Make sure your effective_cache_size is properly configured. Increase random_page_cost and/or decrease seq_page_cost. You can play with the cost settings on a connection, using EXPLAIN on the query, to see what plan you get with each configuration before putting it into the postgresql.conf file. -Kevin That was a good idea. I hadn't tried playing with those settings in a session. This is a 8G box, and we've dedicated half of that (4G) to the file system cache. So, 4G is what effective_cache_size is set to. Our seq_page_cost is set to 1 and our random_page_cost is set to 1.75 in the postgresql.conf. In testing this one particular slow query in a session, I changed these settings alternating in increments of 0.25. The random_page_cost up to 4 and the seq_page_cost down to 0.25. This made perhaps a second difference, but at the end, we were back to to the 37s. Doing a set enable_nestloop=off in the session reduced the runtime to 1.2s with the other settings back to our normal day to day settings. So, for now I think we are going to have to modify the code to prepend the problematic queries with this setting and hope the estimator is able to better estimate this particular query in 8.3. Thanks for the suggestions, -Chris
Re: [PERFORM] Ramifications of turning off Nested Loops for slow queries
On 3/4/08, Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: On Tue, Mar 4, 2008 at 8:42 AM, in message [EMAIL PROTECTED], Chris Kratz [EMAIL PROTECTED] wrote: So, I've now been asked to ping the list as to whether turning off nested loops system wide is a bad idea, and why or why not. In our environment, the fastest plan for a lot of queries involve nested loops. Of course, it's possible that these never provide the fasted plan in your environment, but it seems very unlikely -- you're just not noticing the queries where it's doing fine. Yeah, I seem to recall similar queries from other people who were considering the opposite, ie disabling the other join types :-( The rule of thumb is that nestloop with an inner indexscan will beat anything else for pulling a few rows out of a large table. But on the other hand it loses big for selecting lots of rows. I don't think that a global disable in either direction would be a smart move, unless you run only a very small number of query types and have checked them all. regards, tom lane So, if we can't find another way to solve the problem, probably our best bet is to turn off nested loops on particularly bad queries by prepending them w/ set enable_nested_loop=off? But, leave them on for the remainder of the system? Do you think it's worth testing on 8.3 to see if the estimator is able to make a better estimate? -Chris
Re: [PERFORM] mis-estimate in nested query causes slow runtimes
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote:
Chris Kratz [EMAIL PROTECTED] writes:
The first frustration is that I can't get the transaction details scan
to get any more accurate. It thinks it will find 1407 records,
instead it finds 20,153. Then for whatever reason it thinks that a
join between 1 record and 1407 records will return 1 record. This is
mainly what I can't understand. Why does it think it will only get
one record in response when it's a left join?
I don't see any left join there ...
PG 8.2.4 on Linux kernel 2.6.9 x64
The first thing you should do is update to 8.2.6; we've fixed a fair
number of problems since then that were fallout from the outer-join
planning rewrite in 8.2.
If it still doesn't work very well, please post the pg_stats rows for
the join columns involved (idatrndtl.ida_trans_match_source_id and
mtchsrcprj3.id). (You do have up-to-date ANALYZE stats for both
of those tables, right?)
regards, tom lane
Hello Tom,
We've updated to Postgres 8.2.6 on our production database over the weekend.
Unfortunately, the estimates on this query are no better after the upgrade.
Here is just the part of the estimate that is incorrect. (2 vs 20153)
- Nested Loop (cost=12.68..165.69 rows=2 width=38) (actual time=
0.089..29.792 rows=20153 loops=1)
- Hash Join (cost=12.68..24.37 rows=1 width=24) (actual time=
0.064..0.135 rows=1 loops=1)
Hash Cond: (mtchsrcprj3.funding_source_id = mtchsrcprjfs3.nameid
)
- Seq Scan on project mtchsrcprj3 (cost=0.00..11.22 rows=122
width=8) (actual time=0.002..0.053 rows=122 loops=1)
- Hash (cost=12.61..12.61 rows=6 width=24) (actual time=
0.017..0.017 rows=1 loops=1)
- Index Scan using name_float_lfm_idx on namemaster
mtchsrcprjfs3 (cost=0.00..12.61 rows=6 width=24) (actual
time=0.012..0.013rows=1 loops=1)
Index Cond: (name_float_lfm = 'DWS'::text)
- Index Scan using transaction_details_ida_trans_match_source_id on
transaction_details idatrndtl (cost=0.00..123.72 rows=1408 width=22)
(actual time=0.023..17.128 rows=20153 loops=1)
(Entire explain analyze posted earlier in thread)
Total Query runtime: 35309.298 ms
Same w/ enable_nestloop off: 761.715 ms
I've tried the stats up to 1000 on both columns which causes no differences.
Currently the stats are at 100.
test=# alter table transaction_details alter column
ida_trans_match_source_id set statistics 100;
ALTER TABLE
test=# analyze transaction_details;
ANALYZE
test=# alter table project alter column id set statistics 100;
ALTER TABLE
test=# analyze project;
ANALYZE
Stats rows in pg_stats for these two columns:
test=# select * from pg_stats where tablename = 'transaction_details' and
attname='ida_trans_match_source_id';
schemaname | tablename | attname | null_frac |
avg_width | n_distinct | most_common_vals
| most_common_freqs
|
histogram_bounds
| correlation
+-+---+---+---++--+---+-+-
public | transaction_details | ida_trans_match_source_id | 0.479533 |
4 | 69 |
{818,832,930,937,923,812,931,836,837,829,830,14,809} | {0.1024,0.0991333,
0.0408,0.0232,0.0221,0.0219,0.0207,0.0188667,0.0186667,0.0177667,0.0176667,
0.0130333,0.0118667} |
{6,802,813,813,814,814,815,815,816,816,817,817,827,827,833,835,835,838,838,838,838,838,843,920,921,921,921,921,922,922,924,924,924,924,925,926,926,928,928,934,936,936,936,936,936,938,939,941,941,955,965,967,968,968,974,980}
|0.178655
(1 row)
test=# select * from pg_stats where tablename = 'project' and attname='id';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs |
histogram_bounds
| correlation
+---+-+---+---++--+---++-
public | project | id
Re: [PERFORM] mis-estimate in nested query causes slow runtimes
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote:
Chris Kratz [EMAIL PROTECTED] writes:
- Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual
time=2.932..27.772 rows=20153 loops=1)
- Hash Join (cost=10.89..22.58 rows=1 width=24) (actual
time=0.065..0.134 rows=1 loops=1)
Hash Cond: (mtchsrcprj3.funding_source_id =
mtchsrcprjfs3.nameid)
- Seq Scan on project mtchsrcprj3 (cost=0.00..11.22
rows=122 width=8) (actual time=0.002..0.054 rows=122 loops=1)
- Hash (cost=10.83..10.83 rows=5 width=24) (actual
time=0.017..0.017 rows=1 loops=1)
- Index Scan using name_float_lfm_idx on
namemaster mtchsrcprjfs3 (cost=0.00..10.83 rows=5 width=24) (actual
time=0.012..0.013 rows=1 loops=1)
Index Cond: (name_float_lfm = 'DWS'::text)
- Bitmap Heap Scan on transaction_details idatrndtl
(cost=31.85..121.60 rows=1407 width=22) (actual time=2.864..12.060
rows=20153 loops=1)
Recheck Cond: (idatrndtl.ida_trans_match_source_id =
mtchsrcprj3.id)
- Bitmap Index Scan on
transaction_details_ida_trans_match_source_id (cost=0.00..31.50
rows=1407 width=0) (actual time=2.696..2.696 rows=20153 loops=1)
Index Cond: (idatrndtl.ida_trans_match_source_id =
mtchsrcprj3.id)
The first frustration is that I can't get the transaction details scan
to get any more accurate. It thinks it will find 1407 records,
instead it finds 20,153. Then for whatever reason it thinks that a
join between 1 record and 1407 records will return 1 record. This is
mainly what I can't understand. Why does it think it will only get
one record in response when it's a left join?
I don't see any left join there ...
PG 8.2.4 on Linux kernel 2.6.9 x64
The first thing you should do is update to 8.2.6; we've fixed a fair
number of problems since then that were fallout from the outer-join
planning rewrite in 8.2.
If it still doesn't work very well, please post the pg_stats rows for
the join columns involved (idatrndtl.ida_trans_match_source_id and
mtchsrcprj3.id). (You do have up-to-date ANALYZE stats for both
of those tables, right?)
regards, tom lane
I know it's somewhat premature as we haven't had a chance to do the update
yet, but here is what I did w/ the statistics with the current version for
chuckles and grins just to see if it would make a difference in the plan.
# alter table project alter column id set statistics 1000;
ALTER TABLE
# analyze project;
ANALYZE
# alter table transaction_details alter column ida_trans_match_source_id set
statistics 1000;
ALTER TABLE
# analyze transaction_details;
ANALYZE
# select * from pg_stats where (tablename='project' and attname='id') or
(tablename='transaction_details' and attname='ida_trans_match_source_id');
schemaname | tablename | attname | null_frac |
avg_width | n_distinct |
most_common_vals
|
most_common_freqs
|
histogram_bounds
| correlation
+-+---+---+---++--+++-
public | project | id| 0 |
4 | -1 |
|
|
{6,7,8,12,13,14,15,17,18,19,24,25,26,27,28,29,30,31,32,33,34,35,36,37,41,42,71,72,797,802,803,809,812,813,814,815,816,817,818,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,920,921,922,923,92
Re: [PERFORM] mis-estimate in nested query causes slow runtimes
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote: Chris Kratz [EMAIL PROTECTED] writes: - Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual time=2.932..27.772 rows=20153 loops=1) - Hash Join (cost=10.89..22.58 rows=1 width=24) (actual time=0.065..0.134 rows=1 loops=1) Hash Cond: (mtchsrcprj3.funding_source_id = mtchsrcprjfs3.nameid) - Seq Scan on project mtchsrcprj3 (cost=0.00..11.22 rows=122 width=8) (actual time=0.002..0.054 rows=122 loops=1) - Hash (cost=10.83..10.83 rows=5 width=24) (actual time=0.017..0.017 rows=1 loops=1) - Index Scan using name_float_lfm_idx on namemaster mtchsrcprjfs3 (cost=0.00..10.83 rows=5 width=24) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (name_float_lfm = 'DWS'::text) - Bitmap Heap Scan on transaction_details idatrndtl (cost=31.85..121.60 rows=1407 width=22) (actual time=2.864..12.060 rows=20153 loops=1) Recheck Cond: (idatrndtl.ida_trans_match_source_id = mtchsrcprj3.id) - Bitmap Index Scan on transaction_details_ida_trans_match_source_id (cost=0.00..31.50 rows=1407 width=0) (actual time=2.696..2.696 rows=20153 loops=1) Index Cond: (idatrndtl.ida_trans_match_source_id = mtchsrcprj3.id) The first frustration is that I can't get the transaction details scan to get any more accurate. It thinks it will find 1407 records, instead it finds 20,153. Then for whatever reason it thinks that a join between 1 record and 1407 records will return 1 record. This is mainly what I can't understand. Why does it think it will only get one record in response when it's a left join? I don't see any left join there ... PG 8.2.4 on Linux kernel 2.6.9 x64 The first thing you should do is update to 8.2.6; we've fixed a fair number of problems since then that were fallout from the outer-join planning rewrite in 8.2. If it still doesn't work very well, please post the pg_stats rows for the join columns involved (idatrndtl.ida_trans_match_source_id and mtchsrcprj3.id). (You do have up-to-date ANALYZE stats for both of those tables, right?) regards, tom lane Thanks Tom, we will try the upgrade and see if that makes a difference. -Chris
[PERFORM] mis-estimate in nested query causes slow runtimes
Hello, I've been wrestling w/ a complex query for another developer for awhile today. The problem consistently seems to a mis-estimation of the number of rows resulting from a join. This causes the query early on to think it's only going to be processing 1 row and so it chooses nested loops much of the way up the chain. I've messed w/ statistics targets on some of the columns w/ no increase in the accuracy of the estimates. I've analyzed the tables in question (autovac is running). If I turn off nested loops, the query runs in 1.5 seconds. Otherwise it takes about 37s. With other criteria in the where clause it can take many minutes to return. Here is a subset of the explain analyze that I'm wrestling with. The entire explain is at the end of the email. - Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual time=2.932..27.772 rows=20153 loops=1) - Hash Join (cost=10.89..22.58 rows=1 width=24) (actual time=0.065..0.134 rows=1 loops=1) Hash Cond: (mtchsrcprj3.funding_source_id = mtchsrcprjfs3.nameid) - Seq Scan on project mtchsrcprj3 (cost=0.00..11.22 rows=122 width=8) (actual time=0.002..0.054 rows=122 loops=1) - Hash (cost=10.83..10.83 rows=5 width=24) (actual time=0.017..0.017 rows=1 loops=1) - Index Scan using name_float_lfm_idx on namemaster mtchsrcprjfs3 (cost=0.00..10.83 rows=5 width=24) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (name_float_lfm = 'DWS'::text) - Bitmap Heap Scan on transaction_details idatrndtl (cost=31.85..121.60 rows=1407 width=22) (actual time=2.864..12.060 rows=20153 loops=1) Recheck Cond: (idatrndtl.ida_trans_match_source_id = mtchsrcprj3.id) - Bitmap Index Scan on transaction_details_ida_trans_match_source_id (cost=0.00..31.50 rows=1407 width=0) (actual time=2.696..2.696 rows=20153 loops=1) Index Cond: (idatrndtl.ida_trans_match_source_id = mtchsrcprj3.id) The first frustration is that I can't get the transaction details scan to get any more accurate. It thinks it will find 1407 records, instead it finds 20,153. Then for whatever reason it thinks that a join between 1 record and 1407 records will return 1 record. This is mainly what I can't understand. Why does it think it will only get one record in response when it's a left join? The thing is that we've had this happen a number of times recently with complex nested queries. Most of the time things will run very quickly, but an early mis-estimation by the planner causes it to use nested loops exclusively when hash joins would be more appropriate. Is there anything I can do to improve this short of the set enable_nestloop=off? PG 8.2.4 on Linux kernel 2.6.9 x64 -Chris --- Full explain analyze - Group (cost=336.76..336.82 rows=1 width=328) (actual time=36620.831..36621.176 rows=248 loops=1) - Sort (cost=336.76..336.76 rows=1 width=328) (actual time=36620.828..36620.888 rows=248 loops=1) Sort Key: county, fullname_last_first_mdl, CASE WHEN (COALESCE(fullname_last_first_mdl, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(fullname_last_first_mdl, '0'::text) END || ' '::text) || '-'::text) || ' '::text) || CASE WHEN (COALESCE(ssn, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(ssn, '0'::text) END), system_name_id, ssn, ida_account_id, ida_account_match_source_funding_source_name_float_lfm, ida_account_status, vs_query_27453_212267, vs_query_27453_212252, vs_query_27453_212253, vs_query_27453_212254, vs_query_27453_212255, (COALESCE(vs_query_27453_212267, 0::numeric) + COALESCE(vs_query_27453_212255, 0::numeric)) - Subquery Scan foo (cost=336.72..336.75 rows=1 width=328) (actual time=36614.750..36615.319 rows=248 loops=1) - Sort (cost=336.72..336.72 rows=1 width=255) (actual time=36614.737..36614.798 rows=248 loops=1) Sort Key: cou.validvalue, dem.name_float_lfm - Nested Loop Left Join (cost=194.80..336.71 rows=1 width=255) (actual time=506.599..36611.702 rows=248 loops=1) - Nested Loop Left Join (cost=194.80..332.90 rows=1 width=242) (actual time=506.566..36606.528 rows=248 loops=1) Join Filter: (acc.id = qry27453.ida_account_id) - Nested Loop (cost=30.16..168.13 rows=1 width=82) (actual time=0.461..27.079 rows=248 loops=1) - Nested Loop (cost=30.16..167.85 rows=1 width=90) (actual time=0.453..25.133 rows=248 loops=1) - Nested Loop (cost=30.16..165.94 rows=1 width=77) (actual time=0.441..19.687 rows=970 loops=1) - Nested Loop (cost=30.16..162.90 rows=1 width=40) (actual
Re: [PERFORM] Incorrect estimates on columns
On Wednesday 17 October 2007 20:23, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: On Wednesday 17 October 2007 14:49, Tom Lane wrote: Evidently it's not realizing that every row of par will have a join partner, but why not? I suppose a.activityid is unique, and in most cases that I've seen the code seems to get that case right. Would you show us the pg_stats rows for par.activity and a.activityid? Here are the pg_stats rows for par.activity and a.activityid. Hmm, nothing out of the ordinary there. I poked at this a bit and realized that what seems to be happening is that the a.programid = 171 condition is reducing the selectivity estimate --- that is, it knows that that will filter out X percent of the activity rows, and it assumes that *the size of the join result will be reduced by that same percentage*, since join partners would then be missing for some of the par rows. The fact that the join result doesn't actually decrease in size at all suggests that there's some hidden correlation between the programid condition and the condition on par.provider_lfm. Is that true? Maybe you could eliminate one of the two conditions from the query? Since PG doesn't have any cross-table (or even cross-column) statistics it's not currently possible for the optimizer to deal very well with hidden correlations like this ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Yes, you are correct. Programid is a guard condition to make sure a user cannot look at rows outside of their program. In this particular case the par table only has rows for this agency (at one point in time, all rows were in one table), so I was able to remove the check on programid on a. This causes my example query to run in 200ms. That's wonderful. So, to recap. We had a filter on the join clause which really didn't in this case affect the selectivity of the join table. But the optimizer assumed that the selectivity would be affected causing it to think the join would generate only a few rows. Since it thought that there would be relatively few rows returned, it used a nestloop instead of another type of join that would have been faster with larger data sets. Thanks for all your help. -Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Incorrect estimates on columns
Hello Everyone, I'm struggling to get postgres to run a particular query quickly. It seems that very early on, the planner seems to mis-estimate the number of rows returned by a join which causes it to assume that there is only 1 row as it goes up the tree. It then picks a nested loop join which seems to cause the whole query to be slow. Or at least if I turn off nestloop, it runs in 216ms. explain analyze SELECT 1 FROM rpt_agencyquestioncache_171_0 par right outer join namemaster dem on (par.nameid = dem.nameid and dem.programid = 171) right join activity_parentid_view ses on (par.activity = ses.activityid and ses.programid=171) left join ( select ct0.inter_agency_id,ct0.nameid from rpt_agencyquestioncache_171_0 ct0 join rpt_agencyquestioncache_171_2 ct2 on ct2.participantid =ct0.participantid ) as par30232 on (dem.nameid=par30232.nameid and par30232.inter_agency_id=30232) WHERE ( ( (par.provider_lfm) ='Child Guidance Treatment Centers Inc.')) QUERY PLAN -- Nested Loop Left Join (cost=1990.12..5666.92 rows=1 width=0) (actual time=82.185..91511.455 rows=1199 loops=1) Join Filter: (dem.nameid = ct0.nameid) - Nested Loop Left Join (cost=45.92..1324.06 rows=1 width=4) (actual time=0.973..74.206 rows=1199 loops=1) - Nested Loop (cost=45.92..1323.33 rows=1 width=8) (actual time=0.964..61.264 rows=1199 loops=1) - Hash Join (cost=45.92..1251.07 rows=21 width=8) (actual time=0.948..10.439 rows=1199 loops=1) Hash Cond: (par.activity = a.activityid) - Bitmap Heap Scan on rpt_agencyquestioncache_171_0 par (cost=21.92..1222.19 rows=1247 width=8) (actual time=0.415..3.081 rows=1199 loops=1) Recheck Cond: (provider_lfm = 'Child Guidance Treatment Centers Inc.'::text) - Bitmap Index Scan on rpt_aqc_45604_provider_lfm (cost=0.00..21.61 rows=1247 width=0) (actual time=0.394..0.394 rows=1199 loops=1) Index Cond: (provider_lfm = 'Child Guidance Treatment Centers Inc.'::text) - Hash (cost=19.21..19.21 rows=383 width=4) (actual time=0.513..0.513 rows=383 loops=1) - Index Scan using activity_programid_idx on activity a (cost=0.00..19.21 rows=383 width=4) (actual time=0.034..0.307 rows=383 loops=1) Index Cond: (programid = 171) - Index Scan using nameid_pk on namemaster dem (cost=0.00..3.43 rows=1 width=4) (actual time=0.023..0.036 rows=1 loops=1199) Index Cond: (par.nameid = dem.nameid) Filter: (programid = 171) - Index Scan using activity_session_session_idx on activity_session s (cost=0.00..0.72 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1199) Index Cond: (a.activityid = s.session) - Hash Join (cost=1944.20..4292.49 rows=4029 width=4) (actual time=59.732..74.897 rows=4130 loops=1199) Hash Cond: (ct2.participantid = ct0.participantid) - Seq Scan on rpt_agencyquestioncache_171_2 ct2 (cost=0.00..1747.00 rows=74800 width=4) (actual time=0.008..28.442 rows=74800 loops=1199) - Hash (cost=1893.84..1893.84 rows=4029 width=8) (actual time=5.578..5.578 rows=4130 loops=1) - Bitmap Heap Scan on rpt_agencyquestioncache_171_0 ct0 (cost=55.48..1893.84 rows=4029 width=8) (actual time=0.625..3.714 rows=4130 loops=1) Recheck Cond: (inter_agency_id = 30232) - Bitmap Index Scan on rpt_aqc_45604_inter_agency_id (cost=0.00..54.47 rows=4029 width=0) (actual time=0.609..0.609 rows=4130 loops=1) Index Cond: (inter_agency_id = 30232) Total runtime: 91514.109 ms (27 rows) I've increased statistics to 100 of all pertinent columns in the query to no effect. I've vacuumed and all analyzed all tables in question. Autovac is on. Settings of interest in postgresql.conf: shared_buffers = 1024MB work_mem = 256MB maintenance_work_mem = 256MB random_page_cost = 2.0 PG version: 8.2.4 Server Mem: 2G Ram If I reduce random_page_cost to 1.0, I get the following query plan. QUERY PLAN --- Nested Loop Left Join (cost=20.87..4377.13 rows=1 width=0) (actual time=146.402..29585.011 rows=1199 loops=1) - Nested Loop Left Join (cost=20.87..4376.62 rows=1 width=4) (actual
Re: [PERFORM] Incorrect estimates on columns
On Wednesday 17 October 2007 14:49, Tom Lane wrote:
Chris Kratz [EMAIL PROTECTED] writes:
I'm struggling to get postgres to run a particular query quickly.
The key problem seems to be the join size misestimate here:
- Hash Join (cost=45.92..1251.07 rows=21 width=8)
(actual time=0.948..10.439 rows=1199 loops=1) Hash Cond: (par.activity =
a.activityid)
- Bitmap Heap Scan on
rpt_agencyquestioncache_171_0 par (cost=21.92..1222.19 rows=1247
width=8) (actual time=0.415..3.081 rows=1199 loops=1) - Hash
(cost=19.21..19.21 rows=383 width=4) (actual time=0.513..0.513 rows=383
loops=1)
Evidently it's not realizing that every row of par will have a join
partner, but why not? I suppose a.activityid is unique, and in most
cases that I've seen the code seems to get that case right.
Would you show us the pg_stats rows for par.activity and a.activityid?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Thanks for the reply and help Tom,
activityid is unique on the activity table.
activity on par is a child table to activity, with multiple rows per activityid.
Here are the pg_stats rows for par.activity and a.activityid.
# select * from pg_stats where tablename='activity' and attname='activityid';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs |
histogram_bounds
| correlation
+---++---+---++--+---+-+-
public | activity | activityid | 0 | 4 | -1 |
| |
{232,2497,3137,3854,4210,5282,9318,11396,12265,12495,12760,13509,13753,15000,15379,15661,16791,17230,17703,18427,18987,19449,19846,20322,20574,20926,21210,21501,21733,22276,22519,23262,24197,24512,24898,25616,25893,26175,26700,27141,27509,27759,29554,29819,30160,30699,32343,32975,33227,33493,33753,33980,34208,34534,34780,35007,35235,35641,35922,36315,36678,37998,38343,38667,39046,39316,39778,40314,40587,40884,41187,41860,42124,42399,42892,43313,43546,43802,45408,45740,46030,46406,46655,47031,47556,47881,48190,48528,48810,49038,49319,49704,49978,50543,50916,51857,52134,52380,52691,53011,53356}
|0.703852
(1 row)
# select * from pg_stats where tablename='rpt_agencyquestioncache_171_0' and
attname='activity';
schemaname | tablename | attname | null_frac | avg_width
| n_distinct | most_common_vals
|
most_common_freqs
|
histogram_bounds
|
correlation
Re: [PERFORM] Help with performance on current status column
Hello Richard, Thank you for the response. I did forget to mention that the columns have the following meanings. One, if a begin or end date is null, it means that the role is open ended in that direction. For example, if there is no end date, that means currently the role will go on forever beginning with the start date. Your idea of using minimal and maximum dates is an interesting one and not one that I had considered. I will do some testing later today and see if that makes a difference. The other option I am toying with is simply having a status column which is updated nightly via a cron job. This will probably be the most efficient and can be indexed. I also forgot to say that we have seen this behavior on 2 boxes both on Linux (Red Hat ES Mandrake) and both are running Postgres 8.0 (8.0.1 and 8.0.3). Strangely, after playing with statistics some yesterday (setting from 10 to 100 to 1000 and back to 10 and analyzing), the 8.0.1 machine picks a different plan and runs in a 101.104ms. The larger machine (dual proc Opt, 6 disk raid 10, etc) with 8.0.3 still takes 3-5minutes to run the same query with the same data set even after playing with statistics and repeated analyze on the same table. It just seems odd. It seems it is picking the incorrect plan based off of an overly optimistic estimate of rows returned from the calculation. The other frustration with this is that this sql is machine generated which is why we have some of the awkwardness in the calculation. That calc gets used for a lot of different things including column definitions when people want to see the column on screen. Thanks, -Chris On Wednesday 14 September 2005 05:13 am, Richard Huxton wrote: Chris Kratz wrote: Hello All, We are struggling with a specific query that is killing us. When doing explain analyze on the entire query, we *seem* to be getting killed by the estimated number of rows on a case statement calculation. I've included a snippet from the explain analyze of the much larger query. The line in question, (cost=0.00..106.52 rows=1 width=16) (actual time=0.048..67.728 rows=4725 loops=1) shows that it returned 4700 rows instead of 1 which when coupled with a later join causes the statement to run over 3 minutes.[1] It seems that it thinks that the scan on role_id is going to return 1 row, but in reality returns 4725 rows. The case statement causing the problem uses todays date to see if a particular row is still active. Here is a test case showing how far off the estimate is from the reality. [2] [2] A much simpler statement triggers the incorrect row counts here. explain analyze select * from roles rol where CASE WHEN rol.role_id IS NULL THEN NULL WHEN rol.begin IS NOT NULL and rol.end IS NOT NULL THEN CASE WHEN TIMESTAMP 'now'=rol.begin and TIMESTAMP 'now'=rol.end THEN 'Active' ELSE 'Inactive' END WHEN rol.begin IS NOT NULL THEN CASE WHEN TIMESTAMP 'now'=rol.begin THEN 'Active' ELSE 'Inactive' END WHEN rol.end IS NOT NULL THEN CASE WHEN TIMESTAMP 'now'=rol.end THEN 'Active' ELSE 'Inactive' END ELSE 'Active' END = 'Active' Aside #1 - I'm not entirely clear how role_id can be null since you seemed to be joining against it in the real query. Aside #2 - You're probably better off with CURRENT_DATE since begin/end seem to be dates, rather than TIMESTAMP 'now' - and in any case you wanted timestamp with time zone OK, I think the root of your problem is your use of null to mean not ended or not started (whatever 'not started' means). PostgreSQL has the handy timestamptz value infinity, but only for timestamps and not for dates. I'd probably cheat a little and use an end date of '-12-31' or similar to simulate infinity. Then your test is simply: WHERE ... AND (rol.begin = CURRENT_DATE AND rol.end = CURRENT_DATE) That should estimate simply enough. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Chris Kratz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Help with performance on current status column
Hello All,
We are struggling with a specific query that is killing us. When doing
explain analyze on the entire query, we *seem* to be getting killed by the
estimated number of rows on a case statement calculation.
I've included a snippet from the explain analyze of the much larger query. The
line in question, (cost=0.00..106.52 rows=1 width=16) (actual
time=0.048..67.728 rows=4725 loops=1) shows that it returned 4700 rows
instead of 1 which when coupled with a later join causes the statement to run
over 3 minutes.[1]
It seems that it thinks that the scan on role_id is going to return 1 row, but
in reality returns 4725 rows. The case statement causing the problem uses
todays date to see if a particular row is still active. Here is a test case
showing how far off the estimate is from the reality. [2]
I'm not too surprised to see that the estimate is off because it is
calculated, but does anyone know either how to make the estimate more
accurate so it picks a better plan, or is there a better way to do a status
function based off of the current date so that it is more efficient? I've
played with statistics on this table (racheting them up to 1000) with no
change in the plan.
Any thoughts?
-Chris
[1] explain analyze snippet from larger query
- Nested Loop (cost=0.00..955.70 rows=1 width=204) (actual
time=3096.689..202704.649 rows=17 loops=1)
Join Filter: (inner.nameid = outer.name_id)
- Nested Loop (cost=0.00..112.25 rows=1 width=33) (actual
time=0.271..90.760 rows=4725 loops=1)
- Index Scan using role_definition_description_idx on
role_definition rdf (cost=0.00..5.72 rows=1 width=21) (actual
time=0.215..0.218 rows=1 loops=1)
Index Cond: (description = 'Participant'::text)
Filter: (program_id = 120)
- Index Scan using roles_role_id_idx on roles rol
(cost=0.00..106.52 rows=1 width=16) (actual time=0.048..67.728 rows=4725
loops=1)
Index Cond: (rol.role_id = outer.role_id)
Filter: (CASE WHEN (role_id IS NULL) THEN NULL::text WHEN
((begin IS NOT NULL) AND (end IS NOT NULL)) THEN CASE WHEN
((('now'::text)::date = begin) AND (('now'::text)::date = end)) THEN
'Active'::text ELSE 'Inactive'::text END WHEN (begin IS NOT NULL) THEN CASE
WHEN (('now'::text)::date = begin) THEN 'Active'::text ELSE
'Inactive'::text END WHEN (end IS NOT NULL) THEN CASE WHEN
(('now'::text)::date = end) THEN 'Active'::text ELSE 'Inactive'::text END
ELSE 'Active'::text END = 'Active'::text)
- Nested Loop Left Join (cost=0.00..842.19 rows=97 width=175) (actual
time=6.820..42.863 rows=21 loops=4725)
- Index Scan using namemaster_programid_idx on namemaster dem
(cost=0.00..470.12 rows=97 width=164) (actual time=6.811..42.654 rows=21
loops=4725)
Index Cond: (programid = 120)
Filter: ((name_float_lfm ~~* '%clark%'::text) OR
(metaphone(name_float_lfm, 4) = 'KLRK'::text) OR (soundex(name_float_lfm) =
'C462'::text))
- Index Scan using validanswerid_pk on validanswer ina
(cost=0.00..3.82 rows=1 width=19) (actual time=0.003..0.004 rows=1
loops=99225)
Index Cond: (ina.validanswerid = outer.inactive)
-
[2] A much simpler statement triggers the incorrect row counts here.
explain analyze
select *
from roles rol
where
CASE
WHEN rol.role_id IS NULL
THEN NULL
WHEN rol.begin IS NOT NULL and rol.end IS NOT NULL
THEN
CASE WHEN TIMESTAMP 'now'=rol.begin and TIMESTAMP
'now'=rol.end
THEN 'Active'
ELSE 'Inactive' END
WHEN rol.begin IS NOT NULL
THEN
CASE WHEN TIMESTAMP 'now'=rol.begin
THEN 'Active'
ELSE 'Inactive' END
WHEN rol.end IS NOT NULL
THEN
CASE WHEN TIMESTAMP 'now'=rol.end
THEN 'Active'
ELSE 'Inactive' END
ELSE 'Active'
END = 'Active'
Seq Scan on roles rol (cost=0.00..2368.54 rows=413 width=20) (actual
time=0.046..562.635 rows=79424 loops=1)
Filter: (CASE WHEN (role_id IS NULL) THEN NULL::text WHEN ((begin IS NOT
NULL) AND (end IS NOT NULL)) THEN CASE WHEN (('2005-09-13
16:43:18.721214'::timestamp without time zone = begin) AND ('2005-09-13
16:43:18.721214'::timestamp without time zone = end)) THEN 'Active'::text
ELSE 'Inactive'::text END WHEN (begin IS NOT NULL) THEN CASE WHEN
('2005-09-13 16:43:18.721214'::timestamp without time zone = begin) THEN
'Active'::text ELSE 'Inactive'::text END WHEN (end IS NOT NULL) THEN CASE
WHEN ('2005-09-13 16:43:18.721214'::timestamp without time zone = end)
THEN 'Active'::text ELSE 'Inactive'::text END ELSE 'Active'::text END =
'Active'::text)
Total runtime: 884.456 ms
(3 rows)
--
Chris Kratz
---(end of broadcast)---
TIP 1: if posting/reading through
Re: [PERFORM] Large time difference between explain analyze and normal run
On Thursday 10 February 2005 01:58 pm, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: Does anyone have any idea why there be over a 4s difference between running the statement directly and using explain analyze? Aggregate (cost=9848.12..9848.12 rows=1 width=0) (actual time=4841.231..4841.235 rows=1 loops=1) - Seq Scan on answer (cost=0.00..8561.29 rows=514729 width=0) (actual time=0.011..2347.762 rows=530576 loops=1) Total runtime: 4841.412 ms EXPLAIN ANALYZE's principal overhead is two gettimeofday() kernel calls per plan node execution, so 1061154 such calls here. I infer that gettimeofday takes about 4 microseconds on your hardware ... which seems a bit slow for modern machines. What sort of box is it? regards, tom lane OK, that makes sense. Athlon XP 3000+ 1.5G Mem Is there a way to test the gettimeofday() directly? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Performance Tuning
Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we really need more powerfull processor(s) to gain more performance from postgres. We continue to tune our individual queries where we can, but it seems we still are waiting on the db a lot in our app. When we run most queries, top shows the postmaster running at 90%+ constantly during the duration of the request. The disks get touched occasionally, but not often. Our database on disk is around 2.6G and most of the working set remains cached in memory, hence the few disk accesses. All this seems to point to the need for faster processors. Our question is simply this, is it better to invest in a faster processor at this point, or are there configuration changes to make it faster? I've done some testing with with 4x SCSI 10k and the performance didn't improve, in fact it actually was slower the the sata drives marginally. One of our developers is suggesting we should compile postgres from scratch for this particular processor, and we may try that. Any other ideas? -Chris On this particular development server, we have: Athlon XP,3000 1.5G Mem 4x Sata drives in Raid 0 Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1 Items changed in the postgresql.conf: tcpip_socket = true max_connections = 32 port = 5432 shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each sort_mem=16384 vacuum_mem = 32768 # min 1024, size in KB max_fsm_pages = 6 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~50 bytes each effective_cache_size = 115200 # typically 8KB each random_page_cost = 1# units are one sequential page fetch cost ---(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 Tuning
On Wednesday 09 February 2005 03:38 pm, John Arbash Meinel wrote: ... I'm very surprised you are doing RAID 0. You realize that if 1 drive goes out, your entire array is toast, right? I would recommend doing either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes. grin Yeah, we know. This is a development server and we drop and reload databases regularly (sometimes several times a day). In this case we don't really care about the integrity of the data since it's for our developers to test code against. Also, the system is on a mirrored set of drives. On our live servers we have hardware raid 1 at this point for the data drives. When I/O becomes a bottleneck, we are planning on moving to Raid 10 for the data and Raid 1 for the transaction log with as many drives as I can twist arms for. Up to this point it has been easier just to stuff the servers full of memory and let the OS cache the db in memory. We know that at some point this will no longer work, but for now it is. As a side note, I learned something very interesting for our developers here. We had been doing a drop database and then a reload off a db dump from our live server for test data. This takes 8-15 minutes depending on the server (the one above takes about 8 minutes). I learned through testing that I can use create database template some_other_database and make a duplicate in about 2.5 minutes. which is a huge gain for us. We can load a pristine copy, make a duplicate, do our testing on the duplicate, drop the duplicate and create a new duplicate in less then five mintes. Cool. Probably most important, though is to look at the individual queries and see what they are doing. Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1 Items changed in the postgresql.conf: tcpip_socket = true max_connections = 32 port = 5432 shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each sort_mem=16384 vacuum_mem = 32768 # min 1024, size in KB max_fsm_pages = 6# min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000 # min 100, ~50 bytes each effective_cache_size = 115200# typically 8KB each random_page_cost = 1 # units are one sequential page fetch cost Most of these seem okay to me, but random page cost is *way* too low. This should never be tuned below 2. I think this says an index scan of *all* rows is as cheap as a sequential scan of all rows. and that should never be true. You caught me. I actually tweaked that today after finding a page that suggested doing that if the data was mostly in memory. I have been running it at 2, and since we didn't notice any improvement, it will be going back to 2. What could actually be happening is that you are getting index scans when a sequential scan would be faster. I don't know what you would see, but what does explain analyze select count(*) from blah; say. If it is an index scan, you have your machine mistuned. select count(*) always grabs every row, and this is always cheaper with a sequential scan. John =:- With a random_page_cost set to 1, on a larger table a select count(*) nets this... QUERY PLAN -- Aggregate (cost=9848.12..9848.12 rows=1 width=0) (actual time=4916.869..4916.872 rows=1 loops=1) - Seq Scan on answer (cost=0.00..8561.29 rows=514729 width=0) (actual time=0.011..2624.202 rows=514729 loops=1) Total runtime: 4916.942 ms (3 rows) Now here is a very curious thing. If I turn on timing and run the count without explain analyze, I get... count 514729 (1 row) Time: 441.539 ms How odd. Running the explain adds 4.5s to it. Running the explain again goes back to almost 5s. Now I wonder why that would be different. Changing random cpu cost back to 2 nets little difference (4991.940ms for explain and 496ms) But we will leave it at that for now. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance Tuning
On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote: Chris Kratz [EMAIL PROTECTED] writes: We continue to tune our individual queries where we can, but it seems we still are waiting on the db a lot in our app. When we run most queries, top shows the postmaster running at 90%+ constantly during the duration of the request. The disks get touched occasionally, but not often. Our database on disk is around 2.6G and most of the working set remains cached in memory, hence the few disk accesses. All this seems to point to the need for faster processors. I would suggest looking at the top few queries that are taking the most cumulative time on the processor. It sounds like the queries are doing a ton of logical i/o on data that's cached in RAM. A few indexes might cut down on the memory bandwidth needed to churn through all that data. Hmmm, yes we continue to use indexes judiciously. I actually think we've overdone it in some cases since inserts are starting to slow in some critical areas. Items changed in the postgresql.conf: ... random_page_cost = 1# units are one sequential page fetch cost This makes it nigh impossible for the server from ever making a sequential scan when an index would suffice. What query made you do this? What plan did it fix? Yes, it got set back to 2. I was testing various settings suggested by a posting in the archives and that one didn't get reset. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance Tuning
On Wednesday 09 February 2005 05:08 pm, Merlin Moncure wrote: Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we really need more powerfull processor(s) to gain more performance from postgres. Yes, many apps are not I/O bound (mine isn't). Here are factors that are likely to make your app CPU bound: 1. Your cache hit ratio is very high 2. You have a lot of concurrency. 3. Your queries are complex, for example, doing sorting or statistics analysis For now, it's number 3. Relatively low usage, but very complex sql. 4. Your queries are simple, but the server has to process a lot of them (transaction overhead becomes significant) sequentially. 5. You have context switching problems, etc. On the query side, you can tune things down considerably...try and keep sorting down to a minimum (order on keys, avoid distinct where possible, use 'union all', not 'union'). Basically, reduce individual query time. Other stuff: For complex queries, use views to cut out plan generation. For simple but frequently run queries (select a,b,c from t where k), use parameterized prepared statements for a 50% cpu savings, this may not be an option in some client interfaces. Prepared statements are not something we've tried yet. Perhaps we should look into that in cases where it makes sense. On the hardware side, you will get improvements by moving to Opteron, etc. Merlin Well, that's what we were looking for. --- It sounds like our configuration as it stands is probably about as good as we are going to get with the hardware we have at this point. We are cpu bound reflecting the fact that we tend to have complex statements doing aggregates, sorts and group bys. The solutions appear to primarily be: 1. Going to faster hardware of which probably Opterons would be about the only choice. And even that probably won't be a huge difference. 2. Moving to more materialized views and prepared statements where we can. 3. Continue to tweak the sql behind our app. ---(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
[PERFORM] Long running queries degrade performance
Hello all, My apologies if this is not the right mailing list to ask this question, but we are wondering about general performance tuning principles for our main db server. We have a web app with a postgres backend. Most queries have subsecond response times through the web even with high usage. Every once in awhile someone will run either an ad-hoc query or some other long running db process. For some reason, it seems that a small number 3-4 of these jobs running in parallel absolutely floors our server. In monitoring the jobs, linux (Kernel 2.4) drops the long running jobs priority, but even so they seem to hog the system resources making subsequent requests for everyone else very slow. Our database at this point is almost entirely processor and memory bound because it isn't too large to fit most of the working data into memory yet. There is generally little disk activity when this occurs. These long running processes are almost always complex select statements, not generally inserts or updates. We continue to monitor and rework the bottlenecks, but what is a little scary to us is how easily the database becomes almost completely unresponsive with several large jobs running, especially since we have a large number of users. And it only takes one user trying to view a page with one of these selects clicking multiple times because it doesn't come back quickly to bring our system to it's knees for hours. We are looking to move to Kernel 2.6 and possibly a dedicated multiprocessor machine for postgres towards the end of this year. But, I am wondering if there is anything we can do now to increase the interactive performance while there are long running selects running as well. Are there ways to adjust the priority of backend processes, or things to tweak to maximize interactive throughput for the quick jobs while the long running ones run in the background? Or if worse comes to worse to actually kill long running processes without taking down the whole db as we have had to do on occasion. Our server is a modest 2.4Ghz P4 with mirrored UW SCSI drives and 1G of memory. The db on disk is around 800M and this machine also hosts our web app, so there is some contention for the processor. Does anyone have any suggestions or thoughts on things we could look at? Is a multiprocessor box the only answer, or are there other things we should be looking at hardware wise. Thank you for your time. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Long running queries degrade performance
On Friday 16 April 2004 4:25 pm, Mike Nolan wrote: Given the intermittent nature of the problem and its relative brevity (5-10 seconds), I don't know whether top offers the granularity needed to locate the bottleneck. Our long running processes run on the order of multiple minutes (sometimes for over an hour) and it's expected because the sql can be quite complex over somewhat large datasets. But it's the bringing the server to it's knees, that I'm trying to figure out how to address if we can. In other words, let those long running processes run, but somehow still get decent performance for quick requests. Yours reminds me of what used to happen in our apps back when I worked in java and the garbage collector kicked in. Suddenly everything would stop for 10-15s and then continue on. Sort of makes you think the app froze for some reason. It happens on my development system, and I'm the only one on it. I know I've seen it on the production server, but I think it is a bit more common on the development server, though that may be a case of which system I spend the most time on. (Also, the production server is 1300 miles away with a DSL connection, so I may just be seeing network delays some of the time there.) Interesting. Have you tried running a processor monitor and seeing if you are getting a cpu or disk spike when you get the blips? Postgres has been pretty constant for us in it's average runtime for any particular query. We do get some fluctuation, but I've always attributed that to other things happening in the background. I sometimes run gkrellm off the server just to see what's happening on a macro scale. It's a great early indicator when we are getting slammed one way or another (network, memory, processor, disk, etc). Plus it shows a couple of seconds of history so you can see blips pretty easily. My web app traps double-clicks in javascript and ignores all but the first one. That's because some of the users have mice that give double-clicks even when they only want one click. Hmmm, never thought of doing that. Might be interesting to do something like that in a few key places where we have problems. -- Mike Nolan -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(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] Delete performance on delete from table with inherited tables
Thanks Stephan and Tom for your responses. We have been busy, so I haven't had time to do any further research on this till yesterday. I found that the large number of triggers on the parent or master table were foreign key triggers for each table back to the child tables (update and delete on master, insert on child). The triggers have existed through several versions of postgres and as far as we can tell were automatically created using the references keyword at inception. Yesterday I dropped all the current triggers on parent and children and ran a script that did an alter table add foreign key constraint to each of the 67 child tables with update cascade delete cascade. After this, the delete from the parent where no records existed in the child tables was far more acceptable. Instead of taking hours to do the delete, the process ran for about 5 minutes on my workstation. Removing all constraints entirely reduces this time to a couple of seconds. I am currently evaluating if the foreign key constraints are worth the performance penalty in this particular case. To finish up, it appears that the foreign key implementation has changed since when these first tables were created in our database. Dropping the existing triggers and re-adding the constraints on each table significantly improved performance for us. I do not know enough of the internals to know why this happened. But our experience seems to prove that the newer implementation of foreign keys is more efficient then previous versions. YMMV One other item that was brought up was whether the child tables have the fk column indexed, and the answer was yes. Each had a standard btree index on the foreign key. Explain showed nothing as all the time was being spent in the triggers. Time spent in triggers is not shown in the pg 7.3.4 version of explain (nor would I necessarily expect it to). Thanks for your time, expertise and responses. -Chris On Tuesday 09 March 2004 7:18 pm, Stephan Szabo wrote: On Wed, 3 Mar 2004, Chris Kratz wrote: Which certainly points to the triggers being the culprit. In reading the documentation, it seems like the delete from only... statement should ignore the constraint triggers. But it seems quite obvious from the Delete from only merely means that children of the table being deleted will not have their rows checked against any where conditions and removed for that reason. It does not affect constraint triggers at all. Given I'm guessing it's going to be running about 7000 * 67 queries to check the validity of the delete for 7000 rows each having 67 foreign keys, I'm not sure there's much to do other than hack around the issue right now. If you're a superuser, you could temporarily hack reltriggers on the table's pg_class row to 0, run the delete and then set it back to the correct number. I'm guessing from your message that there's never any chance of a concurrent transaction putting in a matching row in a way that something is marked as deletable when it isn't? -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Delete performance on delete from table with inherited tables
doesn't show the actual time used when analyze is used seems to again point to the after delete triggers as being the culprit. Is there any other way to make this faster then to drop and rebuild all the attached constraints? Is there a way to disable the constraints for a single statement. Because of the unique nature of the data, we know that the inherited tables don't need to be inspected. The table structure has worked quite well up till now and we are hoping to not have to drop our foreign keys and inheritance if possible. Any ideas? Thanks for your time, -Chris -- Chris Kratz Systems Analyst/Programmer VistaShare LLC ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
