Re: [GENERAL] Planner cost adjustments
Here is a last follow-up on adjusting the planner costs calculation 1 - Francisco, you provide me with a clue I was looking for at this point! I learned enough PLpgsql over the last week to reprogram all my processes (not always elegant but it works). By processing individual records using loop, I will be able to move forward with my work and save individual results as they are processed. Furthermore, it is now easier to predict jobs completion times, since the DB uses similar plans for each record. I did a test on a stratified sampling of records and I expect the jobs will run for about two weeks (contrarily to months in previous estimates!-) 2 - PLpgsql helped me to bypass the problem. As PT suggested, I will try to setup a test case that demonstrates the problem and post it to the developers' list so they might figure out what to do in such situation. Thanks to all, Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: June-11-15 10:05 To: Daniel Begin Cc: Bill Moran; pgsql-general@postgresql.org; Tomas Vondra; Melvin Davidson Subject: Re: [GENERAL] Planner cost adjustments Hi Daniel: On Thu, Jun 11, 2015 at 2:38 PM, Daniel Begin wrote: . > The remaining problem seems related to the statistics of some large tables. > On one hand, I might increase the statistic target for these tables to > 500, or even to 1000 and look at the results (but I have doubts it > will help). On the other hand, I could simply force enable_seqscan to > OFF for queries dealing with them. > > Still not sure about the best solution but the problem is definitely > narrower :-) One somehow unrelated point. IIRC your problems where related to queries doing joins with selected sets of indexed IDs on a smaller table which then have to be looked up on some very large tables. ( I'm not able to follow too well which is which, users on changesets, I'm a bit lost ). Given your runtimes are always high ( in the seconds range, so it seems wire speed / latencies are not too much of an issue ) and that selectivity estimates on huge tables are always problematic and may be thwarting your plans you may be able to get faster results splitting your query. If I read your plans correctly, that would be selecting your 600 users in one query and then preparing the changeset query for a single user_id, which should be indexed, and looping it for every user. Given current machines can easily send-receive 600 queries in a second it may lead to a simpler solution. This mean you're using the DB as a somehow inteligent plain old indexed file, but sometimes this is the simpler approach ( heck, some of my code uses algorithms from the tape era as they were the faster way I could do it ). I needed to do this in one of my programs, the optimizer kept selecting bad plans so I did the first query, held the results in memory, and then prepared and executed in a loop from the app, my query was selecting a couple thousand values from submillion table, and joining with a multimillion one, getting a couple hundreds matches per original value. Splitting it made the queries on the big table always run indexed and fast ( and as a side bonus avoided duplicating the parts of the first record in the wire a hundred times, which was nice since the short table was wide and I only needed 3 short fields from the second one, and that made the first query run at wire speed and the second at disk speed ). Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
Hi Daniel: On Thu, Jun 11, 2015 at 2:38 PM, Daniel Begin wrote: . > The remaining problem seems related to the statistics of some large tables. > On one hand, I might increase the statistic target for these tables to 500, > or even to 1000 and look at the results (but I have doubts it will help). On > the other hand, I could simply force enable_seqscan to OFF for queries > dealing with them. > > Still not sure about the best solution but the problem is definitely > narrower :-) One somehow unrelated point. IIRC your problems where related to queries doing joins with selected sets of indexed IDs on a smaller table which then have to be looked up on some very large tables. ( I'm not able to follow too well which is which, users on changesets, I'm a bit lost ). Given your runtimes are always high ( in the seconds range, so it seems wire speed / latencies are not too much of an issue ) and that selectivity estimates on huge tables are always problematic and may be thwarting your plans you may be able to get faster results splitting your query. If I read your plans correctly, that would be selecting your 600 users in one query and then preparing the changeset query for a single user_id, which should be indexed, and looping it for every user. Given current machines can easily send-receive 600 queries in a second it may lead to a simpler solution. This mean you're using the DB as a somehow inteligent plain old indexed file, but sometimes this is the simpler approach ( heck, some of my code uses algorithms from the tape era as they were the faster way I could do it ). I needed to do this in one of my programs, the optimizer kept selecting bad plans so I did the first query, held the results in memory, and then prepared and executed in a loop from the app, my query was selecting a couple thousand values from submillion table, and joining with a multimillion one, getting a couple hundreds matches per original value. Splitting it made the queries on the big table always run indexed and fast ( and as a side bonus avoided duplicating the parts of the first record in the wire a hundred times, which was nice since the short table was wide and I only needed 3 short fields from the second one, and that made the first query run at wire speed and the second at disk speed ). Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
Hi Bill, you are right about not changing two variables at the same time. I first increased the statistics target for problematic columns. It helps but did not provide significant improvement. I then changed the random_page_cost which really improved planner's choices about Seq/Index scan. Since I now get expected Seq/Index scan selection on most tables but larger ones, I am not sure keeping increasing random_page_cost is a good idea. I wonder if it may trigger index scan on smaller table when a Seq scan would have been less expensive. The remaining problem seems related to the statistics of some large tables. On one hand, I might increase the statistic target for these tables to 500, or even to 1000 and look at the results (but I have doubts it will help). On the other hand, I could simply force enable_seqscan to OFF for queries dealing with them. Still not sure about the best solution but the problem is definitely narrower :-) Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Moran Sent: June-11-15 06:51 To: Daniel Begin Cc: pgsql-general@postgresql.org; 'Tomas Vondra'; 'Melvin Davidson' Subject: Re: [GENERAL] Planner cost adjustments On Wed, 10 Jun 2015 17:20:00 -0400 Daniel Begin wrote: > Here is a follow-up on adjusting the planner costs calculation > > -Statistics target of problematic columns were increased from 100 to 200. > -Analyse was ran on all concerned tables (actually ran on the whole > DB) -Random_page_cost was decreased from 4 to 2. Since you changed two things, there's no way to be sure which change led to the improvement. You seem to be assuming that changing the stastics target was what helped. While that _may_ be right, it might also have been the change to random_page_cost. > As a result, about 80% of queries are now using what I would consider > an optimal plan. However, the planner keeps using full table scans for > large tables... > > For instance, I ran a query that should have used an index scan on two > similar test tables. The planner had selected an index scan for the > smaller one and a Seq Scan for larger one. Except for their sizes and > for one field not used in the exercise, the test tables were identical > and indexed on the same field. The smaller test table had 3.26E+10 > records and the larger one > 3.78E+11 records. > > The query looked like... > SELECT cs.user_id, cs.changeset_id, nd.id, nd.version FROM > changesets_selection cs, a_test_table nd WHERE > nd.changeset_id=cs.changeset_id; > > In order to understand why the planner selected the Seq Scan instead > of an Index Scan on the large table (nodes), I ran an EXPLAIN ANALYSE > (on warm > cache) using enable_seqscan set to OFF/ON. > > -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=OFF > -; Nested Loop > (cost=100.58..10210480648.03 rows=194204592 width=40) (actual > time=74.088..278008.579 rows=140112 loops=1) >-> Seq Scan on changesets_selection cs > (cost=100.00..1000110.44 rows=6644 width=24) (actual > time=0.015..4.904 rows=6903 loops=1) >-> Index Scan using nodes_changesetidndx on nodes nd > (cost=0.58..31387.49 rows=29230 width=24) (actual time=12.655..40.259 > rows=20 loops=6903) > Index Cond: (changeset_id = cs.changeset_id) Total runtime: > 278026.196 ms > (5 rows) > > -Completed after less than 5 minutes processing -I assume that > cost=100.00..1000110.44 for the Seq Scan of > changesets_selection is an artefact of setting enable_seqscan=OFF. > -From what I see, the evaluation of rows number is still way off > (1400X) for the large table, even if the statistics target was doubled to 200. > > -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=ON > --; Hash Join > (cost=156171782.28..185673195.13 rows=194204592 width=40) >Hash Cond: (cs.changeset_id = n.changeset_id) >-> Seq Scan on changesets_selection cs (cost=0.00..110.44 > rows=6644 > width=24) >-> Hash (cost=84959952.68..84959952.68 rows=3878771968 width=24) > -> Seq Scan on nodes nd (cost=0.00..84959952.68 > rows=3878771968 > width=24) > (5 rows) > > -Still running after 2:30 hours processing! That is why I did not > provided the actual time and rows (however, actual rows are provided > on first query > plan) > -Not surprisingly, the evaluation of rows number is way off again for > the large table - same stats, same results... > > It seems there is a problem with my large table statistics, even after > increase them to 200. Should I increase the statistic target to 500, > or even to 1000? > Is ther
Re: [GENERAL] Planner cost adjustments
On Wed, 10 Jun 2015 17:20:00 -0400 Daniel Begin wrote: > Here is a follow-up on adjusting the planner costs calculation > > -Statistics target of problematic columns were increased from 100 to 200. > -Analyse was ran on all concerned tables (actually ran on the whole DB) > -Random_page_cost was decreased from 4 to 2. Since you changed two things, there's no way to be sure which change led to the improvement. You seem to be assuming that changing the stastics target was what helped. While that _may_ be right, it might also have been the change to random_page_cost. > As a result, about 80% of queries are now using what I would consider an > optimal plan. However, the planner keeps using full table scans for large > tables... > > For instance, I ran a query that should have used an index scan on two > similar test tables. The planner had selected an index scan for the smaller > one and a Seq Scan for larger one. Except for their sizes and for one field > not used in the exercise, the test tables were identical and indexed on the > same field. The smaller test table had 3.26E+10 records and the larger one > 3.78E+11 records. > > The query looked like... > SELECT cs.user_id, cs.changeset_id, nd.id, nd.version > FROM changesets_selection cs, a_test_table nd > WHERE nd.changeset_id=cs.changeset_id; > > In order to understand why the planner selected the Seq Scan instead of an > Index Scan on the large table (nodes), I ran an EXPLAIN ANALYSE (on warm > cache) using enable_seqscan set to OFF/ON. > > -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=OFF > -; > Nested Loop (cost=100.58..10210480648.03 rows=194204592 width=40) > (actual time=74.088..278008.579 rows=140112 loops=1) >-> Seq Scan on changesets_selection cs > (cost=100.00..1000110.44 rows=6644 width=24) (actual > time=0.015..4.904 rows=6903 loops=1) >-> Index Scan using nodes_changesetidndx on nodes nd > (cost=0.58..31387.49 rows=29230 width=24) (actual time=12.655..40.259 > rows=20 loops=6903) > Index Cond: (changeset_id = cs.changeset_id) > Total runtime: 278026.196 ms > (5 rows) > > -Completed after less than 5 minutes processing > -I assume that cost=100.00..1000110.44 for the Seq Scan of > changesets_selection is an artefact of setting enable_seqscan=OFF. > -From what I see, the evaluation of rows number is still way off (1400X) for > the large table, even if the statistics target was doubled to 200. > > -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=ON > --; > Hash Join (cost=156171782.28..185673195.13 rows=194204592 width=40) >Hash Cond: (cs.changeset_id = n.changeset_id) >-> Seq Scan on changesets_selection cs (cost=0.00..110.44 rows=6644 > width=24) >-> Hash (cost=84959952.68..84959952.68 rows=3878771968 width=24) > -> Seq Scan on nodes nd (cost=0.00..84959952.68 rows=3878771968 > width=24) > (5 rows) > > -Still running after 2:30 hours processing! That is why I did not provided > the actual time and rows (however, actual rows are provided on first query > plan) > -Not surprisingly, the evaluation of rows number is way off again for the > large table - same stats, same results... > > It seems there is a problem with my large table statistics, even after > increase them to 200. Should I increase the statistic target to 500, or even > to 1000? > Is there something else I can trigger to get the appropriate plan? > > Comments/explanations would be appreciated > Daniel > > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Daniel Begin > Sent: June-03-15 06:32 > To: 'Bill Moran' > Cc: pgsql-general@postgresql.org; 'Tomas Vondra'; 'Melvin Davidson' > Subject: Re: [GENERAL] Planner cost adjustments > > Thank Bill, > > About disks performance, all drives are identical and connected using USB3 > connections and yes, I can tweak values and restart Postgres without any > hardship!-) > About seq_page_cost and random_page_cost, I am about to test different lower > values as you and Thomas propose. > Raising the statistics target is a good idea. Since most of the data have a > power law distribution it might affect the statistics. > > I will do as suggested and get back to the community for further comments. I > wished to follow the procedure proposed by PT, just in case I eventually had > to on step 4 (contact PostgreSQL developers so they can improve the > planner). > > And I am still open to other proposal > Daniel > > -Original Messag
Re: [GENERAL] Planner cost adjustments
Here is a follow-up on adjusting the planner costs calculation -Statistics target of problematic columns were increased from 100 to 200. -Analyse was ran on all concerned tables (actually ran on the whole DB) -Random_page_cost was decreased from 4 to 2. As a result, about 80% of queries are now using what I would consider an optimal plan. However, the planner keeps using full table scans for large tables... For instance, I ran a query that should have used an index scan on two similar test tables. The planner had selected an index scan for the smaller one and a Seq Scan for larger one. Except for their sizes and for one field not used in the exercise, the test tables were identical and indexed on the same field. The smaller test table had 3.26E+10 records and the larger one 3.78E+11 records. The query looked like... SELECT cs.user_id, cs.changeset_id, nd.id, nd.version FROM changesets_selection cs, a_test_table nd WHERE nd.changeset_id=cs.changeset_id; In order to understand why the planner selected the Seq Scan instead of an Index Scan on the large table (nodes), I ran an EXPLAIN ANALYSE (on warm cache) using enable_seqscan set to OFF/ON. -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=OFF -; Nested Loop (cost=100.58..10210480648.03 rows=194204592 width=40) (actual time=74.088..278008.579 rows=140112 loops=1) -> Seq Scan on changesets_selection cs (cost=100.00..1000110.44 rows=6644 width=24) (actual time=0.015..4.904 rows=6903 loops=1) -> Index Scan using nodes_changesetidndx on nodes nd (cost=0.58..31387.49 rows=29230 width=24) (actual time=12.655..40.259 rows=20 loops=6903) Index Cond: (changeset_id = cs.changeset_id) Total runtime: 278026.196 ms (5 rows) -Completed after less than 5 minutes processing -I assume that cost=100.00..1000110.44 for the Seq Scan of changesets_selection is an artefact of setting enable_seqscan=OFF. -From what I see, the evaluation of rows number is still way off (1400X) for the large table, even if the statistics target was doubled to 200. -- QUERY PLAN on larger table (nodes) with SET enable_seqscan=ON --; Hash Join (cost=156171782.28..185673195.13 rows=194204592 width=40) Hash Cond: (cs.changeset_id = n.changeset_id) -> Seq Scan on changesets_selection cs (cost=0.00..110.44 rows=6644 width=24) -> Hash (cost=84959952.68..84959952.68 rows=3878771968 width=24) -> Seq Scan on nodes nd (cost=0.00..84959952.68 rows=3878771968 width=24) (5 rows) -Still running after 2:30 hours processing! That is why I did not provided the actual time and rows (however, actual rows are provided on first query plan) -Not surprisingly, the evaluation of rows number is way off again for the large table - same stats, same results... It seems there is a problem with my large table statistics, even after increase them to 200. Should I increase the statistic target to 500, or even to 1000? Is there something else I can trigger to get the appropriate plan? Comments/explanations would be appreciated Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Daniel Begin Sent: June-03-15 06:32 To: 'Bill Moran' Cc: pgsql-general@postgresql.org; 'Tomas Vondra'; 'Melvin Davidson' Subject: Re: [GENERAL] Planner cost adjustments Thank Bill, About disks performance, all drives are identical and connected using USB3 connections and yes, I can tweak values and restart Postgres without any hardship!-) About seq_page_cost and random_page_cost, I am about to test different lower values as you and Thomas propose. Raising the statistics target is a good idea. Since most of the data have a power law distribution it might affect the statistics. I will do as suggested and get back to the community for further comments. I wished to follow the procedure proposed by PT, just in case I eventually had to on step 4 (contact PostgreSQL developers so they can improve the planner). And I am still open to other proposal Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Moran Sent: June-02-15 23:31 To: Daniel Begin Cc: pgsql-general@postgresql.org; 'Tomas Vondra'; 'Melvin Davidson' Subject: Re: [GENERAL] Planner cost adjustments On Tue, 2 Jun 2015 14:01:35 -0400 Daniel Begin wrote: > Here is a follow-up on the step-by-step procedure proposed by PT > > #1 - setup postgresql planner's cost estimate settings for my hardware. > -- > -- > -- > Current parameters values described in section 18.7.2 haven't been > changed except for the effective_cache_size seq_page_cost = 1 > random_page_cost = 4 cpu_tuple_cost = 0.01 cpu_i
Re: [GENERAL] Planner cost adjustments
Daniel Begin wrote: > I can tweak values and restart Postgres without any hardship! Many of the important performance-related settings (especially cost factors) can be adjusted with the SET command to affect just the one connection. This can make experimenting a lot easier. > About seq_page_cost and random_page_cost, I am about to test > different lower values as you and Thomas propose. I have often been unable to get optimal plans without boosting the value of cpu_tuple_cost (in addition to adjusting the page costs and setting effective_cache_size). Generally 0.03 is enough, although I have personally never seen problems with going up to 0.05, and that sometimes fixes a few plans that 0.03 misses. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
Thank Bill, About disks performance, all drives are identical and connected using USB3 connections and yes, I can tweak values and restart Postgres without any hardship!-) About seq_page_cost and random_page_cost, I am about to test different lower values as you and Thomas propose. Raising the statistics target is a good idea. Since most of the data have a power law distribution it might affect the statistics. I will do as suggested and get back to the community for further comments. I wished to follow the procedure proposed by PT, just in case I eventually had to on step 4 (contact PostgreSQL developers so they can improve the planner). And I am still open to other proposal Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Moran Sent: June-02-15 23:31 To: Daniel Begin Cc: pgsql-general@postgresql.org; 'Tomas Vondra'; 'Melvin Davidson' Subject: Re: [GENERAL] Planner cost adjustments On Tue, 2 Jun 2015 14:01:35 -0400 Daniel Begin wrote: > Here is a follow-up on the step-by-step procedure proposed by PT > > #1 - setup postgresql planner's cost estimate settings for my hardware. > -- > -- > -- > Current parameters values described in section 18.7.2 haven't been > changed except for the effective_cache_size seq_page_cost = 1 > random_page_cost = 4 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = > 0.005 cpu_operator_cost = 0.0025 effective_cache_size = 10GB > > I did a bunch of tests on frequently used queries to see how well they > perform - using SET enable_seqscan = ON/OFF. > As described earlier in this tread, the planner use Seq Scan on tables > even if using an Index Scan is in this case 5 times faster! > Here are the logs of EXPLAIN ANALYSE on a query... In an earlier message you mention that the drives are external to the computer. I don't remember details, but external drives can be quite dicey as far as performance goes, depending on the specific technlogy (USB vs. external SATA vs. NAS, for example) as well as some pretty wild variances between different brands of the same technology. See: http://www.databasesoup.com/2012/05/random-page-cost-revisited.html As a result, I'm suspicious that the default values you're using for random_page_cost and seq_page_cost are throwing things off becuase your disks aren't performing like internally connected disks. Correct me if I'm wrong on any of the assumptions I'm making here, but I got the impression that you can tweak values and restart Postgres without any hardship. If that's the case, I'm guessing that raising seq_page_cost (possible to 2) will cause Postgres to make better decisions about what are good plans. My suggestion is to try some different values for those two settings, doing several tests after each change, and see if you can find a set of values that starts getting you good plans. It appears that the planner thinks that it can get better performance by reading from the disk in sequence than by picking random pages, and that makes me think that the difference between seq_page_cost and random_page_cost is bigger than the actual behavior of the drives. More comments below. > osmdump=# SET enable_seqscan = ON; > osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed > FROM changesets WHERE changesets.user_id IN(SELECT id FROM > sample.users); > -- > -- > - > Hash Semi Join (cost=21.50..819505.27 rows=726722 width=24) (actual > time=1574.914..7444.938 rows=338568 loops=1) >Hash Cond: (changesets.user_id = users.id) >-> Seq Scan on changesets (cost=0.00..745407.22 rows=25139722 > width=24) (actual time=0.002..4724.578 rows=25133929 loops=1) >-> Hash (cost=14.00..14.00 rows=600 width=8) (actual > time=0.165..0.165 > rows=600 loops=1) > Buckets: 1024 Batches: 1 Memory Usage: 24kB > -> Seq Scan on users (cost=0.00..14.00 rows=600 width=8) > (actual > time=0.003..0.073 rows=600 loops=1) > Total runtime: 7658.715 ms > (7 rows) > > osmdump=# SET enable_seqscan = OFF; > osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed > FROM changesets WHERE changesets.user_id IN(SELECT id FROM > sample.users); > -- > -- > > Nested Loop (cost=115.94..10001072613.45 rows=726722 > width=24) (actual time=0.268..1490.515 rows=338568 loops=1) >-> HashAggregate (cost=115.50..121.50 rows=600 > width=8)
Re: [GENERAL] Planner cost adjustments
On Tue, 2 Jun 2015 14:01:35 -0400 Daniel Begin wrote: > Here is a follow-up on the step-by-step procedure proposed by PT > > #1 - setup postgresql planner's cost estimate settings for my hardware. > > -- > Current parameters values described in section 18.7.2 haven't been changed > except for the effective_cache_size > seq_page_cost = 1 > random_page_cost = 4 > cpu_tuple_cost = 0.01 > cpu_index_tuple_cost = 0.005 > cpu_operator_cost = 0.0025 > effective_cache_size = 10GB > > I did a bunch of tests on frequently used queries to see how well they > perform - using SET enable_seqscan = ON/OFF. > As described earlier in this tread, the planner use Seq Scan on tables even > if using an Index Scan is in this case 5 times faster! > Here are the logs of EXPLAIN ANALYSE on a query... In an earlier message you mention that the drives are external to the computer. I don't remember details, but external drives can be quite dicey as far as performance goes, depending on the specific technlogy (USB vs. external SATA vs. NAS, for example) as well as some pretty wild variances between different brands of the same technology. See: http://www.databasesoup.com/2012/05/random-page-cost-revisited.html As a result, I'm suspicious that the default values you're using for random_page_cost and seq_page_cost are throwing things off becuase your disks aren't performing like internally connected disks. Correct me if I'm wrong on any of the assumptions I'm making here, but I got the impression that you can tweak values and restart Postgres without any hardship. If that's the case, I'm guessing that raising seq_page_cost (possible to 2) will cause Postgres to make better decisions about what are good plans. My suggestion is to try some different values for those two settings, doing several tests after each change, and see if you can find a set of values that starts getting you good plans. It appears that the planner thinks that it can get better performance by reading from the disk in sequence than by picking random pages, and that makes me think that the difference between seq_page_cost and random_page_cost is bigger than the actual behavior of the drives. More comments below. > osmdump=# SET enable_seqscan = ON; > osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM > changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); > > - > Hash Semi Join (cost=21.50..819505.27 rows=726722 width=24) (actual > time=1574.914..7444.938 rows=338568 loops=1) >Hash Cond: (changesets.user_id = users.id) >-> Seq Scan on changesets (cost=0.00..745407.22 rows=25139722 width=24) > (actual time=0.002..4724.578 rows=25133929 loops=1) >-> Hash (cost=14.00..14.00 rows=600 width=8) (actual time=0.165..0.165 > rows=600 loops=1) > Buckets: 1024 Batches: 1 Memory Usage: 24kB > -> Seq Scan on users (cost=0.00..14.00 rows=600 width=8) (actual > time=0.003..0.073 rows=600 loops=1) > Total runtime: 7658.715 ms > (7 rows) > > osmdump=# SET enable_seqscan = OFF; > osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM > changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); > > > Nested Loop (cost=115.94..10001072613.45 rows=726722 width=24) > (actual time=0.268..1490.515 rows=338568 loops=1) >-> HashAggregate (cost=115.50..121.50 rows=600 width=8) > (actual time=0.207..0.531 rows=600 loops=1) > -> Seq Scan on users (cost=100.00..114.00 > rows=600 width=8) (actual time=0.003..0.037 rows=600 loops=1) >-> Index Scan using changesets_useridndx on changesets > (cost=0.44..1775.54 rows=1211 width=24) (actual time=0.038..2.357 rows=564 > loops=600 > Index Cond: (user_id = users.id) > Total runtime: 1715.517 ms > (6 rows) > > > #2 - Run ANALYZE DATABASE and look at performance/planning improvement. > > -- > I ran ANALYZE DATABASE then rerun the query. It did not produce any > significant improvement according to the EXPLAIN ANALYSE below... > > osmdump=# SET enable_seqscan = ON; > osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM > changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); > > - > Hash Semi Join (cost=21.50..819511.42 rows=729133 width=24) (actual > time=1538.100..7307.743 rows=338568 loops=1) >Hash Cond: (changesets.user_id = users.id) >-> Seq Scan on change
Re: [GENERAL] Planner cost adjustments
Here is a follow-up on the step-by-step procedure proposed by PT #1 - setup postgresql planner's cost estimate settings for my hardware. -- Current parameters values described in section 18.7.2 haven't been changed except for the effective_cache_size seq_page_cost = 1 random_page_cost = 4 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.005 cpu_operator_cost = 0.0025 effective_cache_size = 10GB I did a bunch of tests on frequently used queries to see how well they perform - using SET enable_seqscan = ON/OFF. As described earlier in this tread, the planner use Seq Scan on tables even if using an Index Scan is in this case 5 times faster! Here are the logs of EXPLAIN ANALYSE on a query... osmdump=# SET enable_seqscan = ON; osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); - Hash Semi Join (cost=21.50..819505.27 rows=726722 width=24) (actual time=1574.914..7444.938 rows=338568 loops=1) Hash Cond: (changesets.user_id = users.id) -> Seq Scan on changesets (cost=0.00..745407.22 rows=25139722 width=24) (actual time=0.002..4724.578 rows=25133929 loops=1) -> Hash (cost=14.00..14.00 rows=600 width=8) (actual time=0.165..0.165 rows=600 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 24kB -> Seq Scan on users (cost=0.00..14.00 rows=600 width=8) (actual time=0.003..0.073 rows=600 loops=1) Total runtime: 7658.715 ms (7 rows) osmdump=# SET enable_seqscan = OFF; osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); Nested Loop (cost=115.94..10001072613.45 rows=726722 width=24) (actual time=0.268..1490.515 rows=338568 loops=1) -> HashAggregate (cost=115.50..121.50 rows=600 width=8) (actual time=0.207..0.531 rows=600 loops=1) -> Seq Scan on users (cost=100.00..114.00 rows=600 width=8) (actual time=0.003..0.037 rows=600 loops=1) -> Index Scan using changesets_useridndx on changesets (cost=0.44..1775.54 rows=1211 width=24) (actual time=0.038..2.357 rows=564 loops=600 Index Cond: (user_id = users.id) Total runtime: 1715.517 ms (6 rows) #2 - Run ANALYZE DATABASE and look at performance/planning improvement. -- I ran ANALYZE DATABASE then rerun the query. It did not produce any significant improvement according to the EXPLAIN ANALYSE below... osmdump=# SET enable_seqscan = ON; osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); - Hash Semi Join (cost=21.50..819511.42 rows=729133 width=24) (actual time=1538.100..7307.743 rows=338568 loops=1) Hash Cond: (changesets.user_id = users.id) -> Seq Scan on changesets (cost=0.00..745390.84 rows=25138084 width=24) (actual time=0.027..4620.691 rows=25133929 loops=1) -> Hash (cost=14.00..14.00 rows=600 width=8) (actual time=0.300..0.300 rows=600 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 24kB -> Seq Scan on users (cost=0.00..14.00 rows=600 width=8) (actual time=0.022..0.187 rows=600 loops=1) Total runtime: 7519.254 ms (7 rows) osmdump=# SET enable_seqscan = OFF; osmdump=# EXPLAIN ANALYSE SELECT user_id, id AS changeset_id,closed FROM changesets WHERE changesets.user_id IN(SELECT id FROM sample.users); Nested Loop (cost=115.94..10001090810.49 rows=729133 width=24) (actual time=0.268..1466.248 rows=338568 loops=1) -> HashAggregate (cost=115.50..121.50 rows=600 width=8) (actual time=0.205..0.530 rows=600 loops=1) -> Seq Scan on users (cost=100.00..114.00 rows=600 width=8) (actual time=0.003..0.035 rows=600 loops=1) -> Index Scan using changesets_useridndx on changesets (cost=0.44..1805.83 rows=1215 width=24) (actual time=0.036..2.314 rows=564 loops=600) Index Cond: (user_id = users.id) Total runtime: 1677.447 ms (6 rows) #3 - Run EXPLAIN ANALYZE and look for discrepancies between the estimated and actual times -- Looking at above results, there are obvious discrepancies between expected/actual rows and time!
Re: [GENERAL] Planner cost adjustments
Thank Tomas, Here are few answers to your questions/comments - Write cache is the on-drive write cache and I agree on the risks... I plugged them on a UPS. - Spread over is done with tablespaces - 30% & 40% ratios are that 3 indexes/10 and 4 tables/10 are larger than 10GB - Confused comments about random_page_cost setting? Well, I was just citing the doc I found that was confusing for me (at this stage!-) - Sadly I haven't provided any example queries? Well you are right. When I found this strange behavior a couple of weeks ago, I did not questioned myself too much and did not keep comparison stats, but as things are moving forward, I am expecting the problem to rise again so I asked the list hoping there was a simple "just do that" answer floating around. However, as PT suggested, at one point I will have to run new tests and I'll then let you know :-) - Why are you tuning temp_buffers? Simply because looking at others config (on the web) they tend to have more than the standard value while dealing with large and complex queries - Shouldn't you tune shared_buffers instead? Yes but I forgot to mention that... Shared_buffers = 128MB, since it seems that "on Windows the useful range is 64MB to 512MB". Best regards, Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tomas Vondra Sent: May-29-15 20:19 To: Daniel Begin; 'PT' Cc: pgsql-general@postgresql.org; 'Melvin Davidson' Subject: Re: [GENERAL] Planner cost adjustments Hi, On 05/29/15 22:56, Daniel Begin wrote: > Omg! I was not expecting such a step-by-step procedure, thanks! > I'll follow the guide :-) > > Since I was about to provide a bit of context as asked by Tomas, here > it is for those who are interested... > Best regards, > Daniel > > A bit of the required context... > I am running all this on my personal PC: Windows 64b, i7 chip, 16GB ram. > The PostgreSQL 9.3 cluster is spread over 3X3TB external drives with > write caching. Most tables are static (no insert). > > My largest table looks like this... > Records composed of: 3 bigint, 2 boolean, 1 timestamp and 1 geography type. > Number of records: 387013 > Table size: 369GB > Indexes size: 425GB > - btree(primary key): 125GB > - btree(another field): 86GB > - gist(geography): 241GB > Huh, I haven't really expected that. Especially on a Windows laptop with external drives (I assume 7.2k SATA drives connected using USB or maybe eSATA?). Write cache is the on-drive write cache? Not really a good idea to leave that enabled (volatile cache, so a risk of data loss or data corruption). Also, what do you mean by "spread over"? Are you using tablespaces or some sort of RAID? > Overall, 40% of my table and 30% of indexes do not fit in cache > (effective_cache_size=10GB) but looking at mostly used tables and > indexes, more than 90% of what I use doesn't fit. I don't really understand how you compute the 40% and 30%? You have ~800GB of data+indexes, and only 16GB of RAM, so that's more like 2% of the database size. Or do you measure the hit ratios somehow? > On one hand, according to the documentation > (http://www.postgresql.org/docs/9.3/static/runtime-config-query.html), > with a cache rate like mine, I should probably increase > random_page_cost to better reflect the true cost of random storage reads. I don't follow. Haven't you said in the first post that the database often chooses sequential scans while index scans are way faster? Increasing random_page_cost will only push if further towards sequential scans, making it worse. > On the other hand however, I found that... > (https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) > "This is not where you should start to search for plan problems. > Thet random_page_cost is pretty far down this list (at the end in > fact). If you are getting bad plans, this shouldn't be the first thing > you look at, even though lowering this value may be effective. > Instead, you should start by making sure autovacuum is working > properly, that you are collecting enough statistics, and that you have > correctly sized the memory parameters for your server--all the things > gone over above. After you've done all those much more important > things, ifyou're still getting bad plans then you should see if > lowering random_page_cost is still useful." Well, so maybe you're at the point when tuning random_page_cost is the right next step ... but sadly you haven't provided any example queries, so it's hard to say. Can you choose a few queries and run EXPLAIN ANALYZE on them (and post it to explain.depesz.com, and only put the links here)? > Please find below some the database c
Re: [GENERAL] Planner cost adjustments
Hi, On 05/29/15 22:56, Daniel Begin wrote: Omg! I was not expecting such a step-by-step procedure, thanks! I'll follow the guide :-) Since I was about to provide a bit of context as asked by Tomas, here it is for those who are interested... Best regards, Daniel A bit of the required context... I am running all this on my personal PC: Windows 64b, i7 chip, 16GB ram. The PostgreSQL 9.3 cluster is spread over 3X3TB external drives with write caching. Most tables are static (no insert). My largest table looks like this... Records composed of: 3 bigint, 2 boolean, 1 timestamp and 1 geography type. Number of records: 387013 Table size: 369GB Indexes size: 425GB - btree(primary key): 125GB - btree(another field): 86GB - gist(geography): 241GB Huh, I haven't really expected that. Especially on a Windows laptop with external drives (I assume 7.2k SATA drives connected using USB or maybe eSATA?). Write cache is the on-drive write cache? Not really a good idea to leave that enabled (volatile cache, so a risk of data loss or data corruption). Also, what do you mean by "spread over"? Are you using tablespaces or some sort of RAID? > Overall, 40% of my table and 30% of indexes do not fit in cache > (effective_cache_size=10GB) but looking at mostly used tables and > indexes, more than 90% of what I use doesn't fit. I don't really understand how you compute the 40% and 30%? You have ~800GB of data+indexes, and only 16GB of RAM, so that's more like 2% of the database size. Or do you measure the hit ratios somehow? On one hand, according to the documentation (http://www.postgresql.org/docs/9.3/static/runtime-config-query.html), with a cache rate like mine, I should probably increase random_page_cost to better reflect the true cost of random storage reads. I don't follow. Haven't you said in the first post that the database often chooses sequential scans while index scans are way faster? Increasing random_page_cost will only push if further towards sequential scans, making it worse. On the other hand however, I found that... (https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) "This is not where you should start to search for plan problems. Thet random_page_cost is pretty far down this list (at the end in fact). If you are getting bad plans, this shouldn't be the first thing you look at, even though lowering this value may be effective. Instead, you should start by making sure autovacuum is working properly, that you are collecting enough statistics, and that you have correctly sized the memory parameters for your server--all the things gone over above. After you've done all those much more important things, ifyou're still getting bad plans then you should see if lowering random_page_cost is still useful." Well, so maybe you're at the point when tuning random_page_cost is the right next step ... but sadly you haven't provided any example queries, so it's hard to say. Can you choose a few queries and run EXPLAIN ANALYZE on them (and post it to explain.depesz.com, and only put the links here)? Please find below some the database config's parameters that might be of interest... Best regards, Daniel General config parameters I have modified temp_buffers = 512MB Why are you tuning temp_buffers? Shouldn't you tune shared_buffers instead? I'm not very familiar with Windows, and I vaguely remember issues with larger shared_buffers values, but AFAIK that improved in the recent releases. work_mem = 16MB maintenance_work_mem = 256MB checkpoint_segments = 64 checkpoint_completion_target = 0.8 effective_cache_size = 10GB logging_collector = on track_counts = on autovacuum = on Otherwise, I don't see anything terribly misconfigured. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
Omg! I was not expecting such a step-by-step procedure, thanks! I'll follow the guide :-) Since I was about to provide a bit of context as asked by Tomas, here it is for those who are interested... Best regards, Daniel A bit of the required context... I am running all this on my personal PC: Windows 64b, i7 chip, 16GB ram. The PostgreSQL 9.3 cluster is spread over 3X3TB external drives with write caching. Most tables are static (no insert). My largest table looks like this... Records composed of: 3 bigint, 2 boolean, 1 timestamp and 1 geography type. Number of records: 387013 Table size: 369GB Indexes size: 425GB - btree(primary key): 125GB - btree(another field): 86GB - gist(geography): 241GB Overall, 40% of my table and 30% of indexes do not fit in cache (effective_cache_size=10GB) but looking at mostly used tables and indexes, more than 90% of what I use doesn't fit. On one hand, according to the documentation (http://www.postgresql.org/docs/9.3/static/runtime-config-query.html), with a cache rate like mine, I should probably increase random_page_cost to better reflect the true cost of random storage reads. On the other hand however, I found that... (https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) "This is not where you should start to search for plan problems. Thet random_page_cost is pretty far down this list (at the end in fact). If you are getting bad plans, this shouldn't be the first thing you look at, even though lowering this value may be effective. Instead, you should start by making sure autovacuum is working properly, that you are collecting enough statistics, and that you have correctly sized the memory parameters for your server--all the things gone over above. After you've done all those much more important things, if you're still getting bad plans then you should see if lowering random_page_cost is still useful." Please find below some the database config's parameters that might be of interest... Best regards, Daniel General config parameters I have modified temp_buffers = 512MB work_mem = 16MB maintenance_work_mem = 256MB checkpoint_segments = 64 checkpoint_completion_target = 0.8 effective_cache_size = 10GB logging_collector = on track_counts = on autovacuum = on Here are config parameters related to autovacuum autovacuum_analyze_scale_factor = 0.1 autovacuum_analyze_threshold = 50 autovacuum_freeze_max_age = 2 autovacuum_max_workers = 3 autovacuum_multixact_freeze_max_age = 4 autovacuum_naptime = 1min autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = -1 autovacuum_vacuum_scale_factor = 0.2 autovacuum_vacuum_threshold = 50 vacuum_cost_delay = 0 vacuum_cost_limit = 200 vacuum_cost_page_dirty = 20 vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_defer_cleanup_age = 0 vacuum_freeze_min_age = 5000 vacuum_freeze_table_age = 15000 vacuum_multixact_freeze_min_age = 500 vacuum_multixact_freeze_table_age= 15000 -Original Message- From: PT [mailto:wmo...@potentialtech.com] Sent: May-29-15 16:35 To: Daniel Begin Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Planner cost adjustments On Fri, 29 May 2015 09:39:00 -0400 Daniel Begin wrote: > Hi all, > > Running some queries, I found that the planner often selects > sequential scan instead of an index scan, even if the latter is way > faster (one order of magnitude faster if I consider some tests I made > by setting enable_seqscan = ON/OFF). How can I figure out what > parameter I should adjust to get the planner select an appropriate plan that would better consider my DB setup? > > I had a look at > http://www.postgresql.org/docs/9.3/static/runtime-config-query.html > but at this point it is too much information for me;-) Any rules of > thumb, recipes I could use to select which parameters I should look at first? Here's the correct way to handle this: 1) As mentioned elsewhere, first take the time to ensure that your cost estimate settings are reasonable for your hardware. See section 18.7.2: http://www.postgresql.org/docs/9.4/static/runtime-config-query.html 2) If #1 doesnt' fix it, don't change enable_seqscan. Run a bunch of tests on the query(s) to see how well it performs. Then do ANALYZE DATABASE $insert_name_here; and run all the tests again. If performance/planning improves, then the analyze settings on your server aren't aggressive enough. Make changes to related config settings to fix. 3) If #2 doesn't uncover the problem, run EXPLAIN ANALYZE on all the queries in your test. It takes a bit of understanding to do this step, so you'll want to read up a bit and possibly ask questions if you have trouble interpreting the output, but you're looking
Re: [GENERAL] Planner cost adjustments
On Fri, 29 May 2015 09:39:00 -0400 Daniel Begin wrote: > Hi all, > > Running some queries, I found that the planner often selects sequential scan > instead of an index scan, even if the latter is way faster (one order of > magnitude faster if I consider some tests I made by setting enable_seqscan = > ON/OFF). How can I figure out what parameter I should adjust to get the > planner select an appropriate plan that would better consider my DB setup? > > I had a look at > http://www.postgresql.org/docs/9.3/static/runtime-config-query.html but at > this point it is too much information for me;-) Any rules of thumb, recipes > I could use to select which parameters I should look at first? Here's the correct way to handle this: 1) As mentioned elsewhere, first take the time to ensure that your cost estimate settings are reasonable for your hardware. See section 18.7.2: http://www.postgresql.org/docs/9.4/static/runtime-config-query.html 2) If #1 doesnt' fix it, don't change enable_seqscan. Run a bunch of tests on the query(s) to see how well it performs. Then do ANALYZE DATABASE $insert_name_here; and run all the tests again. If performance/planning improves, then the analyze settings on your server aren't aggressive enough. Make changes to related config settings to fix. 3) If #2 doesn't uncover the problem, run EXPLAIN ANALYZE on all the queries in your test. It takes a bit of understanding to do this step, so you'll want to read up a bit and possibly ask questions if you have trouble interpreting the output, but you're looking for discrepencies between the estimated and actual times for any particular table. If you find them, that tends to indicate that you'll need to update statistics targets on any tables with the problem. See: http://www.postgresql.org/docs/9.4/static/planner-stats.html 4) If #3 doesn't fix things, then the PostgreSQL developers want to know about your problem so they can improve the planner. First, if there are queries that are causing you problems, update the application to disable sequential scans _for_those_particular_ _queries_ so your application continues to trundle along but don't disable sequential scans globally, as that may cause other queries to perform badly. Once that immediate problem is out of the way, put together a test case that demonstrates the problem you're having (but doesn't contain any proprietary data, etc) and post it to the list so the developers can figure out what to do to improve Postgres. Hope this helps. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
Hi, On 05/29/15 17:22, Melvin Davidson wrote: I have found that setting enable_seqscan = off will remedy that situation. Basically, it forces the planner to choose the index. However, if no correct index is available, it will use sequential scan anyway. The only time it will have a negative effect is if the seqscan is actually faster, which doesn't happen very often. Actually, this is quite poor choice, exactly because it forces the planner to use indexscan even if seqscan would be much faster. The correct way to fix this is by tweaking cost variables, for example by setting random_page_cost = 2 (instead of default 4). Sadly, the original post provides very little information about the context, e.g. database size, storage system, explain plans etc. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner cost adjustments
I have found that setting enable_seqscan = off will remedy that situation. Basically, it forces the planner to choose the index. However, if no correct index is available, it will use sequential scan anyway. The only time it will have a negative effect is if the seqscan is actually faster, which doesn't happen very often. On Fri, May 29, 2015 at 9:39 AM, Daniel Begin wrote: > Hi all, > > Running some queries, I found that the planner often selects sequential > scan instead of an index scan, even if the latter is way faster (one order > of magnitude faster if I consider some tests I made by setting > enable_seqscan = ON/OFF). How can I figure out what parameter I should > adjust to get the planner select an appropriate plan that would better > consider my DB setup? > > > > I had a look at > http://www.postgresql.org/docs/9.3/static/runtime-config-query.html but > at this point it is too much information for me;-) Any rules of thumb, > recipes I could use to select which parameters I should look at first? > > > > Daniel > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
[GENERAL] Planner cost adjustments
Hi all, Running some queries, I found that the planner often selects sequential scan instead of an index scan, even if the latter is way faster (one order of magnitude faster if I consider some tests I made by setting enable_seqscan = ON/OFF). How can I figure out what parameter I should adjust to get the planner select an appropriate plan that would better consider my DB setup? I had a look at http://www.postgresql.org/docs/9.3/static/runtime-config-query.html but at this point it is too much information for me;-) Any rules of thumb, recipes I could use to select which parameters I should look at first? Daniel
