ndent bugs
here: first, why (and how) is the temp table different, and second how
does that result in the observed performance problem.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> On Fri, 2006-12-15 at 09:56 -0500, Tom Lane wrote:
>> The fundamental problem with it was the assumption that different
>> executions of a plan node will have the same timing. That's not true,
>> in fact not ev
he view.
Yeah, this is a known issue with UNIONs not preserving the length info
--- which is not entirely unreasonable: what will you do with varchar(40)
union varchar(50)? There's a hack in place as of 8.2 to keep the
length if all the union arms have the same length.
ndividual
> insert to the temp table needs to be sent over the connection and this is
> what degrades over time. I can reproduce on 7.4.6 and 8.1.4. I have a
> small C program to do this which I can send you offline if you're
> interested.
Please.
regard
also consider hash-based implementations.
The hard part of that is not to break DISTINCT ON ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
we PANIC
and can't continue running the database
Once you free some space on the data partition and restart, you should
be good to go --- there will be no loss of committed transactions, since
all the operations are in pg_xlog. Might take a little while to replay
all that log though :-(
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> before the system crash? The scenario we've seen in the past is
>>
>> * data partition out of space, so writes fail
>> * each time Postgres attempts a ch
ohp@pyrenet.fr writes:
> On Fri, 22 Dec 2006, Tom Lane wrote:
>> No, pg_xlog can be truncated as soon as a checkpoint occurs.
> Even for currently running transactions ?
Yes.
regards, tom lane
---(end of broadcast)---
Kevin Hunter <[EMAIL PROTECTED]> writes:
> On 26 Dec 2006 at 2:55p -0500, Tom Lane wrote:
>> I thought you did a fine job right there ;-). In essence this would be
>> replacing one level of indexing with two, which is unlikely to be a win.
>> If you have exactly M ro
uce the per-transaction overhead.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
f the stats were up-to-date), why did it use an indexscan
rather than a seqscan? Are you sure you haven't tweaked any parameters
you didn't tell us about, such as setting enable_seqscan = off?
regards, tom lane
---(end of broadcast)
ad of 42). If it doesn't
want to use an indexscan for this, disable plan types until it does.
This would perhaps shed some light on why 8.2 doesn't want to use a scan
like that as the inside of a nestloop.
regards, tom lane
-
do to help diagnose this problem?
Either dig into it yourself with gdb, or send me a not-too-large example
dump file (off-list)...
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by
. ] I find that explanation pretty implausible, but unless
you can reproduce it on the production machine, I suppose digging
further would be a waste of time ...
regards, tom lane
---(end of broadcast)---
TIP 9: In versions belo
ted iterations of the inner scan.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
provide
useful data about which steps take a long time, if you're not sure.)
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ing connection? Try strace'ing psql (or
whatever the Solaris equivalent is) to see what it's doing.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
t is ... is the table really the same size
in both servers? If so, could we see the pg_stats row for
step_result_subset.uut_result on both servers?
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through U
pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(pg_class)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
I could see this taking an unreasonable amount of time if you had a huge
number of pg_class rows or a very long search_path --- is your data
Erik Jones <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I could see this taking an unreasonable amount of time if you had a huge
>> number of pg_class rows or a very long search_path --- is your database
>> at all out of the ordinary in those ways?
>>
>
ing use of the table-selection switches?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
d do a
test run of "pg_dump -s" with logging enabled; that shouldn't take an
unreasonable amount of time or space.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
> db=> explain analyze select version_id, 'Brc1ccc2nc(cn2c1)C(=O)O' from
> version where version.isosmiles = cansmiles('Brc1ccc2nc(cn2c1)C(=O)O', 1);
And this query is invoking some other, two-argument function; which
apparently hasn't been marked IMMUTA
your own application.
I think one big variable in this is which PG version you are testing.
We've been gradually filing down some of the rough edges in the native
Windows port, so I'd expect that the performance gap is closing over
time. I don't know how close to closed it is in 8.2,
"Dave Dutcher" <[EMAIL PROTECTED]> writes:
> I am looking at upgrading from 8.1.2 to 8.2.0, and I've found a query which
> runs a lot slower.
Um ... what indexes has this table got exactly? It's very unclear what
alternatives the planner is being faced with.
tweaking needed.
BTW, it's interesting to note that the plan 8.1.2 produces is pretty
obviously bogus in itself ... why do only the first two arms of the
BitmapOr use as_of_date conditions? We fixed some sillinesses in the
bitmap scan planning later in the 8.1 series, so I think y
indow,
so if you'd rather your vacuuming mostly happened at 2AM, you still need
a cron job for that.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ne case that has come out on the short end of the stick.
If that's your most important measure of performance, then indeed you
should select a different database that's made different tradeoffs.
regards, tom lane
---(end of broadcast)
; trouble understanding what Informix might have been doing to my (bad
> ?) SQL to "fix" the query.
Me too. Does informix have anything EXPLAIN-like to show what it's
doing?
regards, tom lane
---(end of broadcast)-
sub-SELECT into an IN join even though the sub-SELECT is
correlated to the outer query (that is, it contains outer references).
I'm not sure whether we're just being paranoid by not doing that, or
whether there are special conditions to check before allowing it, or
whether Informix is wrong ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
your query was really a mistake anyway, I'm not sure that your
example is compelling evidence for making it a high priority.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
yzed your tables recently?
regards, tom lane
---(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
is the change from estimating
1 row matching the transaction_date range constraint, to estimating lots
of them, and the join type away up at the top would surely not have
affected that.
regards, tom lane
---(end of broadcast)---
class.reltuples. But it's hard to
believe that'd have caused as big a rowcount shift as we see here ...
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ble and doing work there.
How big is this database (how many pg_class entries)? What do you get
from "VACUUM VERBOSE pg_class"? The truss results make it sound like
the problem is pgstat_vacuum_tabstat() taking a long time, but that code
has not changed since 8.1 ...
d have had a performance issue
with pg_class that large. Also, could we see
select relkind, count(*) from pg_class group by relkind;
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our l
e an
index on tt_log.codcep? If so, maybe you need to cast the result of
the concatenation to char(8) to get it to use the index.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our
oday.
(2) Reconsider whether last-vacuum-time should be sent to the collector
unconditionally.
Comments from hackers?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ave regressed but
I can think of several places that would've been bad before. One is
that there are seqscans of pg_inherits ...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
r $PGDATA/global/pgstat.stat file, anyway?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
the problem in 8.1, though.
This code is just about exactly the same in 8.1. Maybe you changed
your stats collector settings when moving to 8.2?
regards, tom lane
Index: pgstat.c
===
RCS file: /cvsroot/pgsql/s
d figures out what the referencing object is.
I don't really see any solution to that last point :-(
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
ch
parsing (operator resolution to be specific). I think
your client code is failing to re-use prepared statements the way you
think it is.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usene
may find
yourself pessimizing the actual behavior.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
erhaps more to the point, why do you think you need to? pg_xlog should
stay pretty level at approximately 2*checkpoint_segments xlog files
(once it's ramped up to that size, which might take a heavy burst of
activity if checkpoint_segments is large).
regards, tom lane
---
d, the autovac process fired up.
> What could get PG in a state where autovac isn't running?
Um, are you sure it wasn't? The autovac process is not an always-there
thing, it quits after each pass and then the postmaster starts a new one
awhile later.
that data around since about 2001 (PG 7.1 or so,
which is positively medieval compared to current releases). So at
least for static data, it's certainly possible to get useful results.
What are your processing requirements?
regards, tom lane
--
sh because it thinks it won't
fit in memory ...
There is a bug here, I'd say: the rowcount estimate ought to be the same
either way. Dunno why it's not, but will look --- I see the same
misbehavior with a toy table here.
regards, tom lane
-
there isn't any "parent relation" in a UNION, I'm not
sure that this patch actually changed your results ... but I'm not
seeing what else would've ...
regards, tom lane
---(end of broadcast)---
size instead of unconditionally believing
pg_class.relpages/reltuples. Thus, they're much less likely to get
fooled when a table has grown substantially since it was last vacuumed
or analyzed.
regards, tom lane
---(end of broadcast)-
enable_seqscan = off, does that force an indexscan, and if so
what does EXPLAIN ANALYZE show in that case?
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
e is not doing the right thing:
notice the rowcount estimate is 10, whereas it should be only 2 because
of the unique index on f2. I poked into it and realized that in 8.2
scalararraysel() fails to deal with binary-compatible datatype cases,
instead falling back to a not-very-bright generic estima
8.2's new code for
flattening UNION ALL subqueries into "append relations" is failing to
initialize all the fields of the appendrel, which confuses
estimate_num_groups (and perhaps other places). I think this will fix
it for you.
s
checkpoint_timeout (typically 5 minutes or so) and then keep an eye on
the postmaster log for awhile. If you see more than a few "checkpoints
are occuring too frequently" messages, you want to raise
checkpoint_segments.
regards, tom lane
-
n providing
the context they were derived in. I wish he'd stop doing that...
regards, tom lane
---(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
to work for
DISTINCT as well as GROUP BY. IIRC, DISTINCT is currently rather
thoroughly intertwined with ORDER BY, and we'd have to figure out
some way to decouple them --- without breaking DISTINCT ON, which
makes it a lot harder :-(
regards, tom lane
---
(kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes(-u) 266
virtual memory(kbytes, -v) unlimited
$
6 meg of memory isn't gonna hold 7 million rows ... so either raise
"ulimit -d" (quite a lot) or else use a cursor to fet
ulimit -d 614400
Or just "ulimit -d unlimited"
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
mit for, yes?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ly-sized chunks than 7M rows. (If you don't want to mess with
managing a cursor explicitly, as of 8.2 there's a psql variable
FETCH_COUNT that can be set to make it happen behind the scenes.)
regards, tom lane
---(end of broadcast)---
if the data stays inside the database engine.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
ate existing rows using a join,
you can use UPDATE ... FROM (not standard) or something involving a
sub-select. You'd need to state your problem in some detail to get more
help than that...
regards, tom lane
---(end of broadcast)---
you weigh it down with a ton
of index updates, foreign key checks, etc, it could get slow ...
also you haven't mentioned what else that plpgsql function is doing.
regards, tom lane
---(end of broadcast)---
TIP 7: You c
ipe, like which PG
version you're using. But I'm going to guess that it's 8.2.x, because
8.1.x gets it right :-(. Try the attached.
regards, tom lane
Index: planagg.c
===
RCS file: /cvsroot/pg
"Karen Hill" <[EMAIL PROTECTED]> writes:
> On Feb 5, 9:33 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
>> I think you have omitted a bunch of relevant facts.
> The postgres version is 8.2.1 on Windows. The pl/pgsql function is
> inserting to an updatable view (basic
eration for 8.2 or something I should look into correcting?
What version did you update from, and what platform is this?
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Benjamin Minshall <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Benjamin Minshall <[EMAIL PROTECTED]> writes:
>>> Since upgrading to 8.2.3 yesterday, the stats collector process has had
>>> very high CPU utilization; it is consuming roughly 80-90% of on
Benjamin Minshall <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Can you gather some info about what it's doing?
>> strace'ing the stats collector might prove interesting, also if you have
>> built it with --enable-debug then oprofile results would be helpf
n as before. What I'm betting is that it's
storing info on a whole lot more tables than before. Did you decide
to start running autovacuum when you updated to 8.2? How many tables
are visible in the pg_stats views?
regards, tom lane
Benjamin Minshall <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> How many tables are visible in the pg_stats views?
> There are about 15 databases in the cluster each with around 90 tables.
> A count of pg_stats yields between 500 and 800 rows in each database.
Sorry,
y tables? During periods of high activity it could be
> creating temporary tables as often as two per second.
Hmmm ... that's an interesting point, but offhand I don't see why it'd
cause more of a problem in 8.2 than 8.1. Alvaro, any thoughts?
regards, tom la
recently for
performance reasons ... could I have broken it? Anyone want to take a
second look at
http://archives.postgresql.org/pgsql-committers/2007-01/msg00171.php
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
so we can
get some more info? Alternatively, if Benjamin wants to send me a copy
of his stats file (off-list), I'd be happy to take a look.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
roblem
started.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
aken as gospel.
Instead the planner uses the current physical table size in place of
relpages, and scales reltuples correspondingly. So neither steady
growth nor truncation create a need for re-ANALYZE; at least not as long
as the other statistics don't change too much.
rows.
You might be able to get a better result if you increased the statistics
target for ts_status --- it looks like the planner thinks there are many
more ts_status = 3 rows than there really are.
regards, tom lane
---(end of broadcast)---
(bitmapqualorig, qpqual);
What's not immediately clear is why the condition was in both lists to
start with. Perhaps idx_lieu_parking is a partial index with this as
its WHERE condition?
regards, tom lane
---(end of broadcast)
to recheck partial-index conditions even
if the bitmap is not lossy, but I can't reconstruct my reasoning at the
moment.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
" instead of the actual column name?
EXPLAIN can't conveniently get access to the column name. That could
probably be improved if someone wanted to put enough effort into it.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
#x27;available'.
Also, I don't see that you mentioned anywhere what PG version you are
running, but if it's not the latest then an update might help. I recall
having fixed a bug that made the planner too eager to AND on an index
that wouldn't actually help much ... which seems
erest, provide some more context --- EXPLAIN
ANALYZE output for starters.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
nlike all other plan node types, since we haven't actually visited
the heap yet...)
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
g a recheck would be redundant.
Yeah, but his question is why is it in the filter? I think that the
answer is probably "because the index is lossy for this operator,
so it has to be checked even if the bitmap didn't become lossy".
You'd have to check the GIST opclass definition
begin;
update pg_index set indisvalid = false
where indexrelid = 'soandso'::regclass;
explain analyze ...;
rollback;
regards, tom lane
---(end of broadcast)---
TIP 7: You ca
o make CREATE INDEX CONCURRENTLY
work. But if you want to (mis?)use it as a hint, you can ...
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
to make it like the
nestloop plan better...
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
rom students;
> I have indexes on students(studentid) and studentprofile(studentid).
The optimal index for this would be on studentprofile(studentid,score).
A quick test says that PG 8.1 knows what to do with such an index ---
what does EXPLAIN show for this query?
regards
area is to put pg_xlog on a separate
spindle; although that probably is only important for update-intensive
applications. You did not tell us anything about your application...
regards, tom lane
---(end of broadcast)---
TIP
ping on the same sema. Not that
there's anything wrong with his proposal, but it doesn't affect Postgres.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
rmance seems to have deteriorated)
Did you remember to re-ANALYZE everything after loading up the new
database? That's a frequent gotcha ...
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading thro
Benjamin Minshall <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> It's sounding like what you had was just transient bloat, in which case
>> it might be useful to inquire whether anything out-of-the-ordinary had
>> been done to the database right before the excess
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> A further report led us to realize that 8.2.x in fact has a nasty bug
>> here: the stats collector is supposed to dump its stats to a file at
>> most every
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> On 3/2/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
>>> I think this explains the trigger that was blowing up my FC4 box.
>>
>> I dug in th
EXT)) - 1) >= 0 OR
> ...etc...
I think you need to look into full-text indexing (see tsearch2).
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
d less suitable) index for the c1 queries?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
t do you have?
I don't see any need to guess. iostat or vmstat or local equivalent
will show you quick enough if you are maxing out the disk or the CPU.
It seems at least somewhat possible that the thing is blocked on a lock,
in which case the pg_locks view would tell you about it.
n step 4. Just do the CLUSTER and then
ANALYZE (or VACUUM ANALYZE if you really must, but the value is marginal).
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
t;30% slower" bit certainly doesn't
represent an across-the-board figure. As best I can tell, the decisions
the planner happened to be making in late June were peculiarly nicely
suited to his test, but not so much for other cases.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
rows for symptoms.id and symptom_reports.symptom_id?
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
601 - 700 of 4389 matches
Mail list logo
