Re: [PERFORM] Why we don't want hints Was: Slow count(*) again...
On Thu, Feb 10, 2011 at 7:32 PM, Craig James craig_ja...@emolecules.com wrote: On 2/10/11 9:21 AM, Kevin Grittner wrote: Shaun Thomasstho...@peak6.com wrote: how difficult would it be to add that syntax to the JOIN statement, for example? Something like this syntax?: JOIN WITH (correlation_factor=0.3) Where 1.0 might mean that for each value on the left there was only one distinct value on the right, and 0.0 would mean that they were entirely independent? (Just as an off-the-cuff example -- I'm not at all sure that this makes sense, let alone is the best thing to specify. I'm trying to get at *syntax* here, not particular knobs.) There are two types of problems: 1. The optimizer is imperfect and makes a sub-optimal choice. 2. There is theoretical reasons why it's hard for the optimizer. For example, in a table with 50 columns, there is a staggering number of possible correlations. An optimizer can't possibly figure this out, but a human might know them from the start. The City/Postal-code correlation is a good example. For #1, Postgres should never offer any sort of hint mechanism. As many have pointed out, it's far better to spend the time fixing the optimizer than adding hacks. For #2, it might make sense to give a designer a way to tell Postgres stuff that it couldn't possibly figure out. But ... not until the problem is clearly defined. What should happen is that someone writes with an example query, and the community realizes that no amount of cleverness from Postgres could ever solve it (for solid theoretical reasons). Only then, when the problem is clearly defined, should we talk about solutions and SQL extensions. I don't have one such query handy. However, I think your posting is a good starting point for a discussion how to figure out what we need and how a good solution could look like. For example, one thing I dislike about hints is that they go into the query. There are a few drawbacks of this approach - Applications need to be changed to benefit which is not always possible. - One important class of such applications are those that use OR mappers - hinting then would have to be buried in OR mapper code or configuration. - Hints in the query work only for exactly that query (this might be an advantage depending on point of view). I think the solution should rather be to tell Postgres what it couldn't possibly figure out. I imagine that could be some form of description of the distribution of data in columns and / or correlations between columns. Advantage would be that the optimizer gets additional input which it can use (i.e. the usage can change between releases), the information is separate from queries (more like meta data for tables) and thus all queries using a particular table which was augmented with this meta data would benefit. Usage of this meta data could be controlled by a flag per session (as well as globally) so it would be relatively easy to find out whether this meta data has become obsolete (because data changed or a new release of the database is in use). Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Different execution time for same plan
First off, this is posted to the wrong list -- this list is for discussion of development of the PostgreSQL product. There is a list for performance questions where this belongs: pgsql-performance@postgresql.org. I'm moving this to the performance list with a blind copy to the -hackers list so people know where the discussion went. Nick Raj nickrajj...@gmail.com wrote: When i execute the query first time, query takes a quite longer time but second time execution of the same query takes very less time (despite execution plan is same) Why the same plan giving different execution time? (Reason may be data gets buffered (cached) for the second time execution) Why there is so much difference? Because an access to a RAM buffer is much, much faster than a disk access. Which option will be true? It depends entirely on how much of the data needed for the query is cached. Sometimes people will run a set of queries to warm the cache before letting users in. MY postgresql.conf file having setting like this (this is original setting, i haven't modify anything) shared_buffers = 28MB #work_mem = 1MB# min 64kB #maintenance_work_mem = 16MB# min 1MB If you're concerned about performance, these settings (and several others) should probably be adjusted: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] poor performance when recreating constraints on large tables
I originally posted this on admin, but it was suggested to post it to performance so here goes - I am in the process of implementing cascade on delete constraints retroactively on rather large tables so I can cleanly remove deprecated data. The problem is recreating some foreign key constraints on tables of 55 million rows+ was taking much longer than the maintenance window I had, and now I am looking for tricks to speed up the process, hopefully there is something obvious i am overlooking. here is the sql I am running, sorry im trying to obfuscate object names a little - BEGIN; ALTER TABLE ONLY t1 DROP CONSTRAINT fk_t1_t2_id; ALTER TABLE ONLY t1 ADD CONSTRAINT fk_t1_t2_id FOREIGN KEY(id) REFERENCES t2(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; COMMIT; t1 has 55 million rows t2 has 72 million rows the id columns are integer types postgres version 8.3.8 there are nightly vacuum/analyze commands, and auto vacuum is enabled. I have tried set constraints deferred, immediate, the id column on table 2 is indexed, its the primary key. Nothing really seems to impact the time it takes to recreate the constraint. There may be memory settings to tweak, I was able to get it to run on a faster test server with local storage in about 10 minutes, but it was running for over an hour in our production environment.. We took down the application and I verified it wasnt waiting for an exclusive lock on the table or anything, it was running the alter table command for that duration. Let me know if there is anything else I can supply that will help the review, thanks! One additional question - is there any way to check how long postgres is estimating an operation will take to complete while it is running? Thanks again, Mike
Re: [PERFORM] poor performance when recreating constraints on large tables
Mike Broers mbro...@gmail.com writes: I am in the process of implementing cascade on delete constraints retroactively on rather large tables so I can cleanly remove deprecated data. The problem is recreating some foreign key constraints on tables of 55 million rows+ was taking much longer than the maintenance window I had, and now I am looking for tricks to speed up the process, hopefully there is something obvious i am overlooking. maintenance_work_mem? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] not exits slow compared to not in. (nested loops killing me)
Hi all, I am trying to speed up a query on a DB I inherited and I am falling flat on my face . I changed a query from NOT IN to use NOT EXISTS and my query time went from 19000ms to several hours (~5000 ms). this shocked me so much I pretty much had to post. This seems like a corner case of the planner not knowing that the nested-loops are going to turn out badly in this case. The planner choosing a 13hr nested loop here is basically the reason I am posting. I have played around with rewriting this query using some CTEs and a left join but thus far my results are not encouraging. Given what little I know , it seems like a LEFT JOIN where right_table.col is null gets the same performance and estimates as a NOT EXISTS. (and still picks a nested loop in this case) I can see where it all goes to hell time wise, turning off nested loops seems to keep it from running for hours for this query, but not something I am looking to do globally. The time is not really that much better than just leaving it alone with a NOT IN. two queries are at http://pgsql.privatepaste.com/a0b672bab0# the pretty explain versions : NOT IN (with large work mem - 1GB) http://explain.depesz.com/s/ukj NOT IN (with only 64MB for work_mem) http://explain.depesz.com/s/wT0 NOT EXISTS (with 64MB of work_mem) http://explain.depesz.com/s/EuX NOT EXISTS (with nested loop off. and 64MB of work_mem) http://explain.depesz.com/s/UXG LEFT JOIN/CTE (with nested loop off and 1GB of work_mem) http://explain.depesz.com/s/Hwm table defs, with estimated row counts (which all 100% match exact row count) http://pgsql.privatepaste.com/c2ff39b653 tried running an analyze across the whole database, no affect. I haven't gotten creative with explicit join orders yet . postgresql 9.0.2. willing to try stuff for people as I can run things on a VM for days and it is no big deal. I can't do that on production machines. thoughts ? ideas ? -Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] poor performance when recreating constraints on large tables
Thanks for the suggestion, maintenance_work_mem is set to the default of 16MB on the host that was taking over an hour as well as on the host that was taking less than 10 minutes. I tried setting it to 1GB on the faster test server and it reduced the time from around 6-7 minutes to about 3:30. this is a good start, if there are any other suggestions please let me know - is there any query to check estimated time remaining on long running transactions? On Mon, Jun 6, 2011 at 3:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Mike Broers mbro...@gmail.com writes: I am in the process of implementing cascade on delete constraints retroactively on rather large tables so I can cleanly remove deprecated data. The problem is recreating some foreign key constraints on tables of 55 million rows+ was taking much longer than the maintenance window I had, and now I am looking for tricks to speed up the process, hopefully there is something obvious i am overlooking. maintenance_work_mem? regards, tom lane
Re: [PERFORM] 8.4/9.0 simple query performance regression
Josh Berkus j...@agliodbs.com writes: Just got this simple case off IRC today: [ hashed versus non-hashed subplan ] I'm at a bit of a loss as to what's happening here. Possibly work_mem is smaller in the second installation? (If I'm counting on my fingers right, you'd need a setting of at least a couple MB to let it choose a hashed subplan for this case.) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not exits slow compared to not in. (nested loops killing me)
On 06/07/2011 04:38 AM, mark wrote: NOT EXISTS (with 64MB of work_mem) http://explain.depesz.com/s/EuX Hash Anti Join (cost=443572.19..790776.84 rows=1 width=1560) (actual time=16337.711..50358.487 rows=2196299 loops=1) Note the estimated vs actual rows. Either your stats are completely ridiculous, or the planner is confused. What are your stats target levels? Have you tried increasing the stats levels on the table(s) or at least column(s) affected? Or tweaking default_statistics_target if you want to use a bigger hammer? Is autovacuum being allowed to do its work and regularly ANALYZE the database? Does an explicit 'ANALYZE' help? -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not exits slow compared to not in. (nested loops killing me)
Craig Ringer cr...@postnewspapers.com.au writes: On 06/07/2011 04:38 AM, mark wrote: Hash Anti Join (cost=443572.19..790776.84 rows=1 width=1560) (actual time=16337.711..50358.487 rows=2196299 loops=1) Note the estimated vs actual rows. Either your stats are completely ridiculous, or the planner is confused. The latter ... I think the OP is hurting for lack of this 9.0.4 fix: http://git.postgresql.org/gitweb?p=postgresql.gita=commitdiffh=159c47dc7170110a39f8a16b1d0b7811f5556f87 regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not exits slow compared to not in. (nested loops killing me)
-Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Monday, June 06, 2011 5:08 PM To: mark Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] not exits slow compared to not in. (nested loops killing me) On 06/07/2011 04:38 AM, mark wrote: NOT EXISTS (with 64MB of work_mem) http://explain.depesz.com/s/EuX Hash Anti Join (cost=443572.19..790776.84 rows=1 width=1560) (actual time=16337.711..50358.487 rows=2196299 loops=1) Note the estimated vs actual rows. Either your stats are completely ridiculous, or the planner is confused. I am starting to think the planner might be confused in 9.0.2. I got a reasonable query time, given resource constraints, on a very small VM on my laptop running 9.0.4. I am going to work on getting the vm I was using to test this with up to 9.0.4 and test again. There is a note in the 9.0.4 release notes Improve planner's handling of semi-join and anti-join cases (Tom Lane) Not sure that is the reason I got a much better outcome with a much smaller vm. But once I do some more testing I will report back. What are your stats target levels? Have you tried increasing the stats levels on the table(s) or at least column(s) affected? Or tweaking default_statistics_target if you want to use a bigger hammer? Will try that as well. Currently the default stat target is 100. Will try at 250, and 500 and report back. Is autovacuum being allowed to do its work and regularly ANALYZE the database? Does an explicit 'ANALYZE' help? Auto vac is running, I have explicitly vacuum analyzed the whole db. That didn't change anything. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
