Re: [PERFORM] How to force Postgres to use index on ILIKE
SELECT toode, nimetus FROM toode WHERE toode ILIKE 'x10%' ESCAPE '!' ORDER BY UPPER(toode ),nimetus LIMIT 100 runs 1 minute in first time for small table size. Toode field type is CHAR(20) 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you problems using an index, period. 1. I haven't seen any example where VARCHAR is better that CHAR for indexing 2. I have a lot of existing code. Changing CHAR to VARCHAR requires probably re-writing a lot of code, a huge work. 2) You can't use an index on ILIKE. I'ts very sad. I expected that lower(toode) index can be used. You can, however, use an index on lower(field) if your query is properly phrased and if you've created an expression index on lower(field). I tried by Postgres does not use index. Why ? create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops); explain analyze select nimi from firma1.klient where lower(nimi) like 'mokter%' Seq Scan on klient (cost=0.00..9.79 rows=1 width=74) (actual time=0.740..0.761 rows=1 loops=1) Filter: (lower((nimi)::text) ~~ 'mokter%'::text) Total runtime: 0.877 ms ---(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] How to force Postgres to use index on ILIKE
Andrus [EMAIL PROTECTED] writes: 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you problems using an index, period. 1. I haven't seen any example where VARCHAR is better that CHAR for indexing The advice you were given is good, even if the explanation is bad. CHAR(n) is a poor choice for just about every purpose, because of all the padding blanks it insists on storing and transmitting. That adds up to a lot of wasted space, I/O effort, and CPU cycles. I tried by Postgres does not use index. Why ? create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops); Try to get over this fixation on CHAR. That would work with text_pattern_ops --- lower() returns TEXT, and TEXT is what the LIKE operator accepts, so that's the opclass you need to use to optimize lower() LIKE 'pattern'. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Some queries starting to hang
On Mon, 2006-06-05 at 17:06 -0400, Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: On Mon, Jun 05, 2006 at 04:07:19PM -0400, Tom Lane wrote: I'm wondering about out-of-date or nonexistent ANALYZE stats, missing custom adjustments of statistics target settings, etc. But even the nested loop shouldn't be a never returns case, should it? For 1800 rows? Well, it's a big query. If it ought to take a second or two, and instead is taking an hour or two (1800 times the expected runtime), that might be close enough to never to exhaust Chris' patience. Besides, we don't know whether the 1800 might itself be an underestimate (too bad Chris didn't provide EXPLAIN ANALYZE results). This is a good example of a case where the inefficiency of EXPLAIN ANALYZE would be a contributory factor to it not actually being available for diagnosing a problem. Maybe we need something even more drastic than recent proposed changes to EXPLAIN ANALYZE? Perhaps we could annotate the query tree with individual limits. That way a node that was expecting to deal with 1 row would simply stop executing the EXPLAIN ANALYZE when it hit N times as many rows (default=no limit). That way, we would still be able to see a bad plan even without waiting for the whole query to execute - just stop at a point where the plan is far enough off track. That would give us what we need: pinpoint exactly which part of the plan is off-track and see how far off track it is. If the limits were configurable, we'd be able to opt for faster-but-less-accurate or slower-yet-100% accuracy behaviour. We wouldn't need to worry about timing overhead either then. e.g. EXPLAIN ANALYZE ERRLIMIT 10 SELECT ... -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Some queries starting to hang
On Tue, 2006-06-06 at 10:43 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2006-06-05 at 17:06 -0400, Tom Lane wrote: Well, it's a big query. If it ought to take a second or two, and instead is taking an hour or two (1800 times the expected runtime), that might be close enough to never to exhaust Chris' patience. Besides, we don't know whether the 1800 might itself be an underestimate (too bad Chris didn't provide EXPLAIN ANALYZE results). This is a good example of a case where the inefficiency of EXPLAIN ANALYZE would be a contributory factor to it not actually being available for diagnosing a problem. Huh? The problem is the inefficiency of the underlying query. Of course that was the main problem from the OP. You mentioned it would be good if the OP had delivered an EXPLAIN ANALYZE; I agree(d). The lack of EXPLAIN ANALYZE is frequently because you can't get them to run to completion - more so when the query you wish to analyze doesn't appear to complete either. The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Some queries starting to hang
On Mon, Jun 05, 2006 at 01:39:38PM -0700, Chris Beecroft wrote: Thanks Tom, I knew you would come through again! Query is now returning with results on our replicated database. Will vacuum analyze production now. So it seems to have done the trick. Now the question is has our auto vacuum failed or was not set up properly... A question for my IT people. You should almost certainly be running the autovacuum that's built in now. If you enable vacuum_cost_delay you should be able to make it so that vacuum's impact on production is minimal. The other thing you'll want to do is cut all the vacuum threshold and scale settings in half (the defaults are very conservative). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Some queries starting to hang
Simon Riggs [EMAIL PROTECTED] writes: You mentioned it would be good if the OP had delivered an EXPLAIN ANALYZE; I agree(d). The lack of EXPLAIN ANALYZE is frequently because you can't get them to run to completion - more so when the query you wish to analyze doesn't appear to complete either. Well, he could have shown EXPLAIN ANALYZE for the server that was managing to run the query in a reasonable amount of time. The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Some queries starting to hang
On Tue, Jun 06, 2006 at 11:06:09AM -0400, Tom Lane wrote: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. Does that really matter, though? The point is to find the node where the estimate proved to be fantasy. It might even make sense to highlight that node in the output, so that users don't have to wade through a sea of numbers to find it. If it is important to report how far along the query got, it seems that could always be added to the explain output. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some queries starting to hang
On Tue, Jun 06, 2006 at 11:06:09AM -0400, Tom Lane wrote: it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. Well, you _might_ get something useful, if you're trying to work on a maladjusted production system, because you get to the part that trips the limit, and then you know, Well, I gotta fix it that far, anyway. Often, when you're in real trouble, you can't or don't wait for the full plan to come back from EXPLAIN ANALYSE, because a manager is helpfully standing over your shoulder asking whether you're there yet. Being able to say, Aha, we have the first symptom, might be helpful to users. Because the impatient simply won't wait for the full report to come back, and therefore they'll end up flying blind instead. (Note that the impatient is not always the person logged in and executing the commands.) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some queries starting to hang
Simon Riggs [EMAIL PROTECTED] writes: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... An alternate approach would be to implement a SIGINFO handler that prints out the explain analyze output for the data built up so far. You would be able to keep hitting C-t and keep getting updates until the query completes or you decided to hit C-c. I'm not sure how easy this would be to implement but it sure would be nice from a user's point of view. Much nicer than having to specify some arbitrary limit before running the query. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Some queries starting to hang
Simon Riggs wrote: Well, it's a big query. If it ought to take a second or two, and instead is taking an hour or two (1800 times the expected runtime), that might be close enough to never to exhaust Chris' patience. Besides, we don't know whether the 1800 might itself be an underestimate (too bad Chris didn't provide EXPLAIN ANALYZE results). This is a good example of a case where the inefficiency of EXPLAIN ANALYZE would be a contributory factor to it not actually being available for diagnosing a problem. This is a frustration I have, but Simon expressed it much more concisely. The first question one gets in this forum is, did you run EXPLAIN ANALYZE? But if EXPLAIN ANALYZE never finishes, you can't get the information you need to diagnose the problem. Simon's proposal, e.g. EXPLAIN ANALYZE ERRLIMIT 10 SELECT ... or something similar, would be a big help. I.e. If you can't finish in a reasonable time, at least tell me as much as you can. Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Some queries starting to hang
Tom Lane wrote: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. But at least you have some data, which is better than no data. Even knowing that the plan got stuck on a particular node of the query plan could be vital information. For a query that never finishes, you can't even find out where it's getting stuck. That's why Simon's proposal might help in some particularly difficult situations. Regards, Craig ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Some queries starting to hang
On Tue, 2006-06-06 at 12:50, Craig A. James wrote: Tom Lane wrote: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. But at least you have some data, which is better than no data. Even knowing that the plan got stuck on a particular node of the query plan could be vital information. For a query that never finishes, you can't even find out where it's getting stuck. That's why Simon's proposal might help in some particularly difficult situations. Hm. I wonder if it be hard to have explain analyze have a timeout per node qualifier? Something that said if it takes more than x milliseconds for a node to kill the explain analyze and list the up to the nasty node that's using all the time up? That would be extremely useful. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] vacuuming problems continued
Andrew Sullivan wrote:
On Thu, Jun 01, 2006 at 01:54:08PM +0200, Antoine wrote:
Hi,
We just don't seem to be getting much benefit from autovacuum. Running
a manual vacuum seems to still be doing a LOT, which suggests to me
that I should either run a cron job and disable autovacuum, or just
run a cron job on top of autovacuum.
Don't know if this was covered in an earlier thread. Bear with me if so.
I'm working with 7.4.8 and 8.0.3 systems, and pg_autovacuum does have some
glitches ... in part solved by the integrated autovac in 8.1:
- in our env, clients occasionally hit max_connections. This is a known and
(sort of) desired pushback on load. However, that sometimes knocks pg_autovacuum
out.
- db server goes down for any reason: same problem.
Just restarting pg_autovacuum is not good enough; when pg_autovacuum terminates,
it loses its state, so big tables that change less than 50% between such
terminations may never get vacuumed (!)
For that reason, it's taken a switch to a Perl script run from cron every 5
minutes, that persists state in a table. The script is not a plug-compatible
match for pg_autovacuum (hardcoded rates; hardcoded distinction between user and
system tables), but you may find it useful.
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.
#!/usr/bin/env perl
use strict;
use warnings;
use Carp;
use DBI;
use POSIX qw(strftime);
# Hardcoded (aggressive) autovacuum parameters:
my ($VAC_BASE, $VAC_RATE) = (1000, 0.8);
my ($ANA_BASE, $ANA_RATE) = ( 500, 0.4);
my $VERBOSE = 'VERBOSE';
my $start = time;
my $stamp = strftime %FT%T autovac: , localtime;
open STDERR, STDOUT; # Redirect PG VERBOSE output.
my $dbh = DBI-connect(dbi:Pg(PrintError=1,RaiseError=1):);
# REVISIT: move this to schema:
my $oid = $dbh-selectall_arrayref(__SQL__)-[0][0];
SELECT oid FROM pg_class WHERE relname = 'autovac_state';
__SQL__
$dbh-do(__SQL__) if !defined $oid;
CREATE TABLE public.autovac_state(
relid oid NOT NULL PRIMARY KEY,
nametext NOT NULL,
analyze_timetimestamptz,
vacuum_time timestamptz,
analyze_tups bigint, -- (n_tup_ins+n_tup_upd+n_tup_del) at last ANALYZE
vacuum_tups bigint -- (n_tup_upd+n_tup_del) at last VACUUM
);
__SQL__
# Calculate updates/additions to autovac_state:
my $anavac = join ;, map {$_-[0]} @{$dbh-selectall_arrayref(__SQL__)};
SELECT * INTO TEMP new_state FROM (
SELECT
relid, -- identify tables by ID, so that (re)created
tables always
-- are treated as fresh tables.
name, -- for constructing the vacuum/analyze command
old_relid, -- NULL means this will need a new state table entry
analyze_tups,-- _tups are used to update autovac_state
vacuum_tups,
CASE WHEN analyze_tups - prev_analyze_tups NOT BETWEEN 0 AND
analyze_point
OR old_relid IS NULL
THEN now()
END AS analyze_time,
CASE WHEN vacuum_tups - prev_vacuum_tups NOT BETWEEN 0 AND
vacuum_point
THEN now()
END AS vacuum_time
FROM (
SELECT N.nspname || '.' || C.relname AS name,
A.relid AS old_relid,
C.oid AS relid,
S.n_tup_ins + S.n_tup_upd + S.n_tup_del AS analyze_tups,
S.n_tup_upd + S.n_tup_del AS vacuum_tups,
COALESCE(A.analyze_tups,0) AS
prev_analyze_tups,
COALESCE(A.vacuum_tups,0) AS
prev_vacuum_tups,
CASE WHEN nspname ~ '^pg_' THEN 1.0 ELSE $ANA_RATE END
* C.reltuples + $ANA_BASE AS
analyze_point,
CASE WHEN nspname ~ '^pg_' THEN 2.0 ELSE $VAC_RATE END
* C.reltuples + $VAC_BASE AS vacuum_point
FROMpg_classAS C
JOINpg_namespaceAS N ON N.oid = C.relnamespace
JOINpg_stat_all_tables AS S ON S.relid = C.oid
LEFT JOIN autovac_state AS A ON A.relid = S.relid
WHERE N.nspname NOT LIKE 'pg_temp%'
) AS X
) AS X
WHERE analyze_time IS NOT NULL OR vacuum_time IS NOT NULL;
SELECT CASE WHEN vacuum_time IS NOT NULL
THEN 'VACUUM ANALYZE $VERBOSE ' || name
ELSE 'ANALYZE $VERBOSE ' || name
END
FROM new_state;
__SQL__
if ($anavac) {
print STDERR $stamp.start\n;
$dbh-do(__SQL__);
$anavac;
UPDATE autovac_state
SET analyze_tups
Re: [PERFORM] vacuuming problems continued
Mischa Sandberg wrote: Andrew Sullivan wrote: On Thu, Jun 01, 2006 at 01:54:08PM +0200, Antoine wrote: Hi, We just don't seem to be getting much benefit from autovacuum. Running a manual vacuum seems to still be doing a LOT, which suggests to me that I should either run a cron job and disable autovacuum, or just run a cron job on top of autovacuum. Don't know if this was covered in an earlier thread. Bear with me if so. I'm working with 7.4.8 and 8.0.3 systems, and pg_autovacuum does have some glitches ... in part solved by the integrated autovac in 8.1: - in our env, clients occasionally hit max_connections. This is a known and (sort of) desired pushback on load. However, that sometimes knocks pg_autovacuum out. That is when you use: superuser_reserved_connections In the postgresql.conf - db server goes down for any reason: same problem. I believe you can use stats_reset_on_server_start = on For that little problem. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] vacuuming problems continued
Joshua D. Drake wrote: - in our env, clients occasionally hit max_connections. This is a known and (sort of) desired pushback on load. However, that sometimes knocks pg_autovacuum out. That is when you use: superuser_reserved_connections Blush. Good point. Though, when we hit max_connections on 7.4.8 systems, it's been a lemonade-from-lemons plus that vacuuming didn't fire up on top of everything else :-) - db server goes down for any reason: same problem. I believe you can use stats_reset_on_server_start = on We do. The problem is not the loss of pg_stat_user_tables.(n_tup_ins,...) It's the loss of pg_autovacuum's CountAtLastVacuum (and ...Analyze) numbers, which are kept in process memory. Never considered patching pg_autovacuum to just sleep and try again, rather than exit, on a failed db connection. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Some queries starting to hang
On Tue, Jun 06, 2006 at 12:54:27PM -0500, Scott Marlowe wrote: On Tue, 2006-06-06 at 12:50, Craig A. James wrote: Tom Lane wrote: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. But at least you have some data, which is better than no data. Even knowing that the plan got stuck on a particular node of the query plan could be vital information. For a query that never finishes, you can't even find out where it's getting stuck. That's why Simon's proposal might help in some particularly difficult situations. Hm. I wonder if it be hard to have explain analyze have a timeout per node qualifier? Something that said if it takes more than x milliseconds for a node to kill the explain analyze and list the up to the nasty node that's using all the time up? That would be extremely useful. Maybe, maybe not. It would be very easy for this to croak on the first sort it hits. I suspect the original proposal of aborting once a rowcount estimate proves to be way off is a better idea. For the record, I also think being able to get a current snapshot is great, too. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Some queries starting to hang
On Tue, 2006-06-06 at 15:51, Jim C. Nasby wrote: On Tue, Jun 06, 2006 at 12:54:27PM -0500, Scott Marlowe wrote: On Tue, 2006-06-06 at 12:50, Craig A. James wrote: Tom Lane wrote: The idea I just had was: why do we need EXPLAIN ANALYZE to run to completion? In severe cases like this thread, we might be able to discover the root cause by a *partial* execution of the plan, as long as it was properly instrumented. That way, the OP might have been able to discover the root cause himself... I don't think that helps, as it just replaces one uncertainty by another: how far did the EXPLAIN really get towards completion of the plan? You still don't have any hard data. But at least you have some data, which is better than no data. Even knowing that the plan got stuck on a particular node of the query plan could be vital information. For a query that never finishes, you can't even find out where it's getting stuck. That's why Simon's proposal might help in some particularly difficult situations. Hm. I wonder if it be hard to have explain analyze have a timeout per node qualifier? Something that said if it takes more than x milliseconds for a node to kill the explain analyze and list the up to the nasty node that's using all the time up? That would be extremely useful. Maybe, maybe not. It would be very easy for this to croak on the first sort it hits. I suspect the original proposal of aborting once a rowcount estimate proves to be way off is a better idea. For the record, I also think being able to get a current snapshot is great, too. I can see value in both. Just because the row count is right doesn't mean it won't take a fortnight of processing. :) The problem with the row count estimate being off from the real thing is you only get it AFTER the set is retrieved for that node. The cost of aborting on the first sort is minimal. You just turn up the number for the timeout and run it again. 1 minute or so wasted. The cost of not aborting on the first sort is that you may never see what the part of the plan is that's killing your query, since you never get the actual plan. ---(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] Some queries starting to hang
Scott Marlowe [EMAIL PROTECTED] writes: The cost of not aborting on the first sort is that you may never see what the part of the plan is that's killing your query, since you never get the actual plan. Well, you can get the plan without waiting a long time; that's what plain EXPLAIN is for. But I still disagree with the premise that you can extrapolate anything very useful from an unfinished EXPLAIN ANALYZE run. As an example, if the plan involves setup steps such as sorting or loading a hashtable, cancelling after a minute might make it look like the setup step is the big problem, distracting you from the possibility that the *rest* of the plan would take weeks to run if you ever got to it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Some queries starting to hang
On Tue, 2006-06-06 at 16:11, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: The cost of not aborting on the first sort is that you may never see what the part of the plan is that's killing your query, since you never get the actual plan. Well, you can get the plan without waiting a long time; that's what plain EXPLAIN is for. But I still disagree with the premise that you can extrapolate anything very useful from an unfinished EXPLAIN ANALYZE run. As an example, if the plan involves setup steps such as sorting or loading a hashtable, cancelling after a minute might make it look like the setup step is the big problem, distracting you from the possibility that the *rest* of the plan would take weeks to run if you ever got to it. Sure, but it would be nice to see it report the partial work. i.e. I got to using a nested loop, thought there would be 20 rows, processed 250,000 or so, timed out at 10 minutes, and gave up. I would find that useful. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] lowering priority automatically at connection
Tom Lane wrote: That guy doesn't actually have the foggiest idea what he's doing. The reason there is no built-in capability to do that is that it *does not work well*. Search the list archives for priority inversion to find out why not. I agree that that particular author seems clueless, but better researched papers do show benefits as well: The CMU paper Priority Mechanisms for OLTP and Transactional Web Applications [1] studied both TPC-C and TPC-W workloads on postgresql (as well as DB2). For PostgreSQL they found that without priority inheritance they had factor-of-2 benefits for high-priority transactions; and with priority inheritance they had factor-of-6 benefits for high priority transactions -- both with negligible harm to the low priority transactions. Unless there's something wrong with that paper (and at first glance it looks like their methodologies apply at least to many workloads) it seems that it *does not work well* is a bit of a generalization; and that databases with TPC-C and TPC-W like workloads may indeed be cases where this feature would be useful. [1] http://www.cs.cmu.edu/~harchol/Papers/actual-icde-submission.pdf ...This paper analyzes and proposes prioritization for transactional workloads in conventional DBMS...This paper provides a detailed resource utilization breakdown for OLTP workloads executing on a range of database platforms including IBM DB2[14], Shore[16], and PostgreSQL[17] ... For DBMS using MVCC (with TPC-C or TPC-W workloads) and for TPC-W workloads (with any concurrency control mechanism), we find that lock scheduling is largely ineffective (even preemptive lock scheduling) and CPU scheduling is highly effective. For example, we find that for PostgreSQL running under TPC-C, the simplest CPU scheduling algorithm CPU-Prio provides a factor of 2 improvement for the high-priority transactions, and adding priority inheritance (CPU-Prio-Inherit) brings this up to a factor of near 6 improvement under high loads, while hardly penalizing low-priority transactions. Or am I missing something? Ron ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
