Re: [PERFORM] ported application having performance issues
On Thu, 30 Jun 2005, John Mendenhall wrote: > Our setting for effective_cache_size is 2048. > > random_page_cost = 4, effective_cache_size = 2048 time approximately 4500ms > random_page_cost = 3, effective_cache_size = 2048 time approximately 1050ms > random_page_cost = 3, effective_cache_size = 4096 time approximately 1025ms > > The decrease of random_page_cost to 3 caused the plan > to work properly, using the lead_requests table as a > join starting point and using the contacts index. The effective_cache_size still looks small. As a rule of tumb you might want effective_cache_size to be something like 1/2 or 2/3 of your total memory. I don't know how much you had, but effective_cache_size = 4096 is only 32M. shared_buffers and effective_cache_size is normally the two most important settings in my experience. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] planner picking more expensive plan
Hi,
I've just been referred here after a conversion on IRC and everybody
seemed to think I've stumbled upon some strangeness.
The planner (in PG version 8.0.2) is choosing what it thinks is a more
expensive plan. I've got a table of animals (about 3M rows) and their
movements (about 16M rows), and I'm trying to execute this query:
SELECT a.birthlocnid, m.locnid
FROM animals a
LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0)
LIMIT 10;
If I have "work_mem" set to something small (1000) it uses this plan:
QUERY PLAN
Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10
loops=1)
-> Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual
time=0.211..0.576 rows=10 loops=1)
Merge Cond: ("outer".animalid = "inner".animalid)
-> Index Scan using animals_pkey on animals a
(cost=0.00..10198983.91 rows=3302780 width=8) (actual time=0.112..0.276 rows=10
loops=1)
-> Index Scan using movement_animal on movements m
(cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235 rows=10
loops=1)
Filter: (mtypeid = 0)
Total runtime: 0.413 ms
But if I increase "work_mem" to 1 it uses this plan:
QUERY PLAN
Limit (cost=565969.42..566141.09 rows=10 width=8) (actual
time=27769.047..27769.246 rows=10 loops=1)
-> Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8)
(actual time=27769.043..27769.228 rows=10 loops=1)
Merge Cond: ("outer".animalid = "inner".animalid)
-> Index Scan using movement_animal on movements m
(cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154 rows=10
loops=1)
Filter: (mtypeid = 0)
-> Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual
time=27768.991..27769.001 rows=10 loops=1)
Sort Key: a.animalid
-> Seq Scan on animals a (cost=0.00..77086.80 rows=3302780
width=8) (actual time=0.039..5620.651 rows=3303418 loops=1)
Total runtime: 27851.097 ms
I've tried playing with the statistics as people suggested on IRC but to
no effect. There was some discussion about why it would be doing this,
but nothing obvious came out of it.
SHOW ALL output is at the end of this mail but it should be pretty
standard apart from:
shared_buffers = 1
work_mem = 8192
max_connections = 100
effective_cache_size = 1
Hope that's enough information to be useful.
Thanks.
Sam
name |setting
+
add_missing_from | on
archive_command| /home/postgres/pgarchive "%p"
australian_timezones | off
authentication_timeout | 60
bgwriter_delay | 200
bgwriter_maxpages | 100
bgwriter_percent | 1
block_size | 8192
check_function_bodies | on
checkpoint_segments| 3
checkpoint_timeout | 300
checkpoint_warning | 30
client_encoding| SQL_ASCII
client_min_messages| notice
commit_delay | 0
commit_siblings| 5
config_file| /home/pgdata/postgresql.conf
cpu_index_tuple_cost | 0.001
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
custom_variable_classes| unset
data_directory | /home/pgdata
DateStyle | ISO, MDY
db_user_namespace | off
deadlock_timeout | 1000
debug_pretty_print | off
debug_print_parse | off
debug_print_plan | off
debug_print_rewritten | off
debug_shared_buffers | 0
default_statistics_target | 10
default_tablespace | unset
default_transaction_isolation | read committed
default_transaction_read_only | off
default_with_oids | on
dynamic_library_path | $libdir
effective_cache_size | 1
enable_hashagg | on
enable_hashjoin| on
enable_indexscan | on
enable_mergejoin | on
enable_nestloop| on
enable_seqscan | off
enable_sort| on
enable_tidscan | on
explain_pretty_print | on
external_pid_file | unset
extra_float_digits | 0
from_collapse_limit| 8
fsync | on
geqo | on
geqo_effort| 5
geqo_generations | 0
geqo_pool_size | 0
geqo_selection_bias| 2
geqo_threshold | 12
hba_file | /home/pgdata/pg_hba.conf
ident_file | /home/pgdata/pg_ident.conf
integer_datetimes | off
join_collapse_limi
Re: [PERFORM] planner picking more expensive plan
Sam Mason <[EMAIL PROTECTED]> writes: > The planner (in PG version 8.0.2) is choosing what it thinks is a more > expensive plan. I fooled around trying to duplicate this behavior, without success. Can you create a self-contained test case? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] planner picking more expensive plan
Tom Lane wrote: >I fooled around trying to duplicate this behavior, without success. >Can you create a self-contained test case? I'll try and see if I can put something together, it's probably going to be early next week though. I wont be able to give you our data, so I'll be a bit of a headscratching exercise generating something that'll provoke the same behaviour. Not sure if it'll help, but here's what the database schema looks like at the moment: Table "public.animals" Column| Type | Modifiers -+---+--- animalid| integer | not null sex | character(1) | not null dob | date | not null birthlocnid | integer | breedid | character varying(8) | eartag_1| character varying(20) | eartag_2| character varying(20) | eartag_3| character varying(20) | Indexes: "animals_pkey" primary key, btree (animalid) "animal_birthlocn" btree (birthlocnid) "animal_breed" btree (breedid) "animal_eartag" btree (eartag_1) Check constraints: "animal_sex" CHECK (sex = 'M'::bpchar OR sex = 'F'::bpchar) Table "public.movements" Column | Type | Modifiers --+-+--- locnid | integer | not null animalid | integer | not null movedate | date| not null mtypeid | integer | not null Indexes: "movement_animal" btree (animalid) "movement_location" btree (locnid) "movement_movedate" btree (movedate) "movement_movetype" btree (mtypeid) Foreign-key constraints: "movement_location" FOREIGN KEY (locnid) REFERENCES locations(locnid) "movement_animal" FOREIGN KEY (animalid) REFERENCES animals(animalid) "movement_type" FOREIGN KEY (mtypeid) REFERENCES k_movement_type(mtypeid) Table "public.locations" Column | Type | Modifiers +---+--- locnid | integer | not null ptype | character varying(8) | ltype | character varying(8) | not null cph| character varying(20) | unk| integer | Indexes: "locations_pkey" primary key, btree (locnid) "location_cph" btree (cph) "location_ltype" btree (ltype) "location_ptype" btree (ptype) Foreign-key constraints: "location_ptype" FOREIGN KEY (ptype) REFERENCES k_premise_type(ptypeid) "location_ltype" FOREIGN KEY (ltype) REFERENCES k_location_type(ltypeid) As I said, animals contains about 3M rows, movements about 16M rows and locations about 80K rows. There are about 3 to 8 rows for each and every animal in the movements table, with at most one entry of mtypeid=0 for each animal (95% of the animals have an entry). Not sure if that's going to help making some demo data. It's just that it took quite a while loading it all here, so coming up with some code to make demo data may take a while. Thanks! Sam ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] planner picking more expensive plan
Sam Mason wrote:
Hi,
I've just been referred here after a conversion on IRC and everybody
seemed to think I've stumbled upon some strangeness.
The planner (in PG version 8.0.2) is choosing what it thinks is a more
expensive plan. I've got a table of animals (about 3M rows) and their
movements (about 16M rows), and I'm trying to execute this query:
SELECT a.birthlocnid, m.locnid
FROM animals a
LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0)
LIMIT 10;
Why are you using LIMIT without having an ORDER BY?
What are actually trying to get out of this query? Is it just trying to
determine where the 'home' locations are?
It just seems like this query isn't very useful. As it doesn't restrict
by animal id, and it just gets 10 randomly selected animals where
m.mtypeid=0.
And why a LEFT JOIN instead of a normal join?
Anyway, the general constraints you are applying seem kind of confusing.
What happens if you change the plan to:
SELECT a.birthlocnid, m.locnid
FROM animals a
LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0)
ORDER BY a.animalid LIMIT 10;
I would guess that this would help the planner realize it should try to
use an index, since it can realize that it wants only a few rows by
a.animalid in order.
Though I also recognize that you aren't returning a.animalid so you
don't really know which animals you are returning.
I get the feeling you are trying to ask something like "do animals stay
at their birth location", or at least "how are animals moving around". I
don't know what m.typeid = 0 means, but I'm guessing it is something
like where their home is.
Anyway, I would say you need to put a little bit more restriction in, so
the planner can figure out how to get only 10 rows.
John
=:->
If I have "work_mem" set to something small (1000) it uses this plan:
QUERY PLAN
Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10
loops=1)
-> Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual
time=0.211..0.576 rows=10 loops=1)
Merge Cond: ("outer".animalid = "inner".animalid)
-> Index Scan using animals_pkey on animals a (cost=0.00..10198983.91
rows=3302780 width=8) (actual time=0.112..0.276 rows=10 loops=1)
-> Index Scan using movement_animal on movements m
(cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235 rows=10
loops=1)
Filter: (mtypeid = 0)
Total runtime: 0.413 ms
But if I increase "work_mem" to 1 it uses this plan:
QUERY PLAN
Limit (cost=565969.42..566141.09 rows=10 width=8) (actual
time=27769.047..27769.246 rows=10 loops=1)
-> Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8)
(actual time=27769.043..27769.228 rows=10 loops=1)
Merge Cond: ("outer".animalid = "inner".animalid)
-> Index Scan using movement_animal on movements m
(cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154 rows=10
loops=1)
Filter: (mtypeid = 0)
-> Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual
time=27768.991..27769.001 rows=10 loops=1)
Sort Key: a.animalid
-> Seq Scan on animals a (cost=0.00..77086.80 rows=3302780
width=8) (actual time=0.039..5620.651 rows=3303418 loops=1)
Total runtime: 27851.097 ms
I've tried playing with the statistics as people suggested on IRC but to
no effect. There was some discussion about why it would be doing this,
but nothing obvious came out of it.
SHOW ALL output is at the end of this mail but it should be pretty
standard apart from:
shared_buffers = 1
work_mem = 8192
max_connections = 100
effective_cache_size = 1
Hope that's enough information to be useful.
Thanks.
Sam
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] planner picking more expensive plan
John A Meinel wrote: >Why are you using LIMIT without having an ORDER BY? I'm just exploring the data, trying to figure out what it's like. >It just seems like this query isn't very useful. As it doesn't restrict >by animal id, and it just gets 10 randomly selected animals where >m.mtypeid=0. Yup, that's the point. Check to see if the animals were born where they say they were. The data's come from an external source and I'm just trying to figure out how good it is before I do too much with it >And why a LEFT JOIN instead of a normal join? I'm not sure if some animals will have missing data! >Anyway, the general constraints you are applying seem kind of confusing. This was a slightly cut down query in an attempt to reduce general confusion -- I guess I failed. Sorry! >I would guess that this would help the planner realize it should try to >use an index, since it can realize that it wants only a few rows by >a.animalid in order. This seems to work the appropiate magic. It always seems to prefer index scans now. The real point of asking this question orignally was to find out why the planner was choosing a more expensive plan over a cheaper one. When I discovered this orignally I was disabling seqscan and then it picked the correct version. The actual work_mem didn't change when I did this, it just picked the correct plan. I discovered the work_mem parameter fiddle later. I think I forgot to mention that in the original email though! Sam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] ported application having performance issues
Dennis, On Fri, 01 Jul 2005, Dennis Bjorklund wrote: > On Thu, 30 Jun 2005, John Mendenhall wrote: > > > Our setting for effective_cache_size is 2048. > > > > random_page_cost = 4, effective_cache_size = 2048 time approximately > > 4500ms > > random_page_cost = 3, effective_cache_size = 2048 time approximately > > 1050ms > > random_page_cost = 3, effective_cache_size = 4096 time approximately > > 1025ms > > The effective_cache_size still looks small. As a rule of tumb you might > want effective_cache_size to be something like 1/2 or 2/3 of your total > memory. I don't know how much you had, but effective_cache_size = 4096 is > only 32M. > > shared_buffers and effective_cache_size is normally the two most important > settings in my experience. I have increased the effective_cache_size to 16384 (128M). I have kept random_page_cost at 3 for now. This appears to give me the performance I need at this time. In the future, we'll look at other methods of increasing the performance. Thank you all for all your suggestions. JohnM -- John Mendenhall [EMAIL PROTECTED] surf utopia internet services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Planner constants for RAM resident databases
I'm working with an application where the database is entirely resident in RAM (the server is a quad opteron with 16GBytes of memory). It's a web application and handles a high volume of queries. The planner seems to be generating poor plans for some of our queries which I can fix by raising cpu_tuple_cost. I have seen some other comments in the archives saying that this is a bad idea but is that necessarily the case when the database is entirely resident in RAM? Emil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Planner constants for RAM resident databases
On Fri, Jul 01, 2005 at 09:59:38PM -0400, Emil Briggs wrote: > I'm working with an application where the database is entirely resident in > RAM > (the server is a quad opteron with 16GBytes of memory). It's a web > application and handles a high volume of queries. The planner seems to be > generating poor plans for some of our queries which I can fix by raising > cpu_tuple_cost. I have seen some other comments in the archives saying that > this is a bad idea but is that necessarily the case when the database is > entirely resident in RAM? If I'm understanding correctly that'll mostly increase the estimated cost of handling a row relative to a sequential page fetch, which sure sounds like it'll push plans in the right direction, but it doesn't sound like the right knob to twiddle. What do you have random_page_cost set to? Cheers, Steve ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Planner constants for RAM resident databases
Emil Briggs wrote: I'm working with an application where the database is entirely resident in RAM (the server is a quad opteron with 16GBytes of memory). It's a web application and handles a high volume of queries. The planner seems to be generating poor plans for some of our queries which I can fix by raising cpu_tuple_cost. I have seen some other comments in the archives saying that this is a bad idea but is that necessarily the case when the database is entirely resident in RAM? Emil Generally, the key knob to twiddle when everything fits in RAM is random_page_cost. If you truly have everything in RAM you could set it almost to 1. 1 means that it costs exactly the same to go randomly through the data then it does to go sequential. I would guess that even in RAM it is faster to go sequential (since you still have to page and deal with L1/L2/L3 cache, etc). But the default random_page_cost of 4 is probably too high for you. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Planner constants for RAM resident databases
Emil Briggs wrote: I just mentioned random_page_cost, but you should also tune effective_cache_size, since that is effectively most of your RAM. It depends what else is going on in the system, but setting it as high as say 12-14GB is probably reasonable if it is a dedicated machine. With random_page_cost 1.5-2, and higher effective_cache_size, you should be doing pretty well. John =:-> I tried playing around with these and they had no effect. It seems the only thing that makes a difference is cpu_tuple_cost. I'm surprised. I know cpu_tuple_cost can effect it as well, but usually the recommended way to get indexed scans is the above two parameters. When you do "explain analyze" of a query that you have difficulties with, how are the planner's estimates. Are the estimated number of rows about equal to the actual number of rows? If the planner is mis-estimating, there is a whole different set of tuning to do to help it estimate correctly. John =:-> PS> Use reply-all so that your comments go to the list. signature.asc Description: OpenPGP digital signature
Re: [PERFORM] planner picking more expensive plan
On Fri, 1 Jul 2005, Sam Mason wrote:
The key thing with the query that Sam have is that if you turn off seqscan
you get the first plan that run in 0.4ms and if seqscan is on the runtime
is 27851ms.
There are 100 way to make it select the seq scan, including rewriting the
query to something more useful, tweaking different parameters and so on.
The interesting part is that pg give the fast plan a cost of 202 and the
slow a cost of 566141, but still it chooses the slow query unless seqscan
is turned off (or some other tweak with the same effect). It know very
well that the plan with the index scan will be much faster, it just don't
manage to generate it unless you force it to.
It makes you wonder if pg throws away some plans too early in the planning
phase.
> Limit (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10
> loops=1)
>-> Merge Left Join (cost=0.00..6628.30 rows=3302780 width=8) (actual
> time=0.211..0.576 rows=10 loops=1)
> Merge Cond: ("outer".animalid = "inner".animalid)
> -> Index Scan using animals_pkey on animals a
> (cost=0.00..10198983.91 rows=3302780 width=8) (actual time=0.112..0.276
> rows=10 loops=1)
> -> Index Scan using movement_animal on movements m
> (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.088..0.235
> rows=10 loops=1)
>Filter: (mtypeid = 0)
> Total runtime: 0.413 ms
>
> Limit (cost=565969.42..566141.09 rows=10 width=8) (actual
> time=27769.047..27769.246 rows=10 loops=1)
>-> Merge Right Join (cost=565969.42..57264070.77 rows=3302780 width=8)
> (actual time=27769.043..27769.228 rows=10 loops=1)
> Merge Cond: ("outer".animalid = "inner".animalid)
> -> Index Scan using movement_animal on movements m
> (cost=0.00..56642740.73 rows=3107737 width=8) (actual time=0.022..0.154
> rows=10 loops=1)
>Filter: (mtypeid = 0)
> -> Sort (cost=565969.42..574226.37 rows=3302780 width=8) (actual
> time=27768.991..27769.001 rows=10 loops=1)
>Sort Key: a.animalid
>-> Seq Scan on animals a (cost=0.00..77086.80 rows=3302780
> width=8) (actual time=0.039..5620.651 rows=3303418 loops=1)
> Total runtime: 27851.097 ms
Another thing to notice is that if one remove the Limit node then the
situation is reversed and the plan that pg choose (with the Limit node) is
the one with the lowest cost. The startup cost is however very high so
combining that Merge Join with a Limit will of course produce something
slow compared to the upper plan where the startup cost is 0.0.
A stand alone test case would be nice, but even without the above plans
are interesting.
--
/Dennis Björklund
---(end of broadcast)---
TIP 8: explain analyze is your friend
