true that we can
ignore disclaimers as far as receiving, redistributing, and archiving
mail list submissions goes. On the other hand, accepting a patch is
another matter.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ly earlier rows in symptom_reports contain the same small set
of symptom_ids, but the stats don't seem to indicate such a skew.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will i
ries that benefit from the rows being in nonrandom
order, so I'm not entirely sure that this is a good thing to do ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
<[EMAIL PROTECTED]> writes:
> I was able to improve response time by seting enable_seqscan to off
enable_nestloop = off would probably be a saner choice, at least for
this particular query.
regards, tom lane
---(end of
t enough to avoid that. I'm not sure what tricks are
available for bulk loading with our JDBC driver --- the page Heikki
mentioned explains things from a server perspective but I dunno how that
translates into JDBC. The folks who hang out on pgsql-jdbc could
probably give you some tips.
y working like that he shouldn't see the network utilization
load he reported ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
/postgres/release.html
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
difference from what I think you're trying to do is the lack of
any per-row round trips to the client code. But you need to look into
where the bottleneck is, not just assume it's insoluble.
regards, tom lane
---(end of broadcast)
actice --- I'm not sure if the stored
t_len has always been maxaligned or not.)
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Vincenzo Romano <[EMAIL PROTECTED]> writes:
> How can I delay the query planner decisions until the actual query is to be
> done inside the function body?
Use plpgsql's EXECUTE. AFAIR there is no way in a SQL-language function.
> BTW does make any sense to bother marking trigger functions as STABLE or
> IMMUTABLE?
No, the trigger mechanisms don't pay any attention to that. I can
hardly conceive of a useful trigger that wouldn't be VOLATILE anyway,
since side effects are more or less the point
e process
address space until it's actually touched by that process.
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
er the cost of executing a function. There's one, for example, on Oct
> 18, 2006.
You mean
http://archives.postgresql.org/pgsql-performance/2006-10/msg00283.php
? I don't see anything there that bears on Steve's suggestion.
(The complaint is obsolete as of
"Craig A. James" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> You mean
>> http://archives.postgresql.org/pgsql-performance/2006-10/msg00283.php
>> ? I don't see anything there that bears on Steve's suggestion.
> Mea culpa, it's October
tical applications.
Red Hat does support postgres 8.1 on RHEL4:
http://www.redhat.com/appstack/
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
es that you may be using something older that's
overestimating the cost of the nestloop way.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
"D"."caseNo" LIKE '2006TR%'
> )
> ;
> The commercial product scans the index on caseNo in TranDetail to build a
> work table of unique values, then uses indexed access to the TranHeader and
> then to Adjustment.
If you want that, try
#x27;m not sure that will
help much.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> If you want that, try rewriting the EXISTS to an IN:
>>
>> AND ("H"."tranNo", "H"."countyNo") IN
>> (
>&g
transaction setting, I suspect there may be some basic
application misdesign involved here. How many tables have you got?
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
gt; estimated a number of created tables of about 4000.
> Could be this the problem?
If you have transactions that touch many of them within one transaction,
then yup, you could be out of locktable space. Try increasing
max_locks_per_transaction.
regards, tom lane
mance improvement, quit worrying
about this micro-detail and get yourself onto a more modern Postgres.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe
sses were
generating all the I/O or the CPU load?
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
message can get through to the mailing list cleanly
t_on_server_start turned on?
The drop in reported transaction rate is still baffling though. Are you
sure you're really doing the same amount of work? Can you estimate what
you think the transaction rate *should* be from a what-are-your-clients-
doing perspective?
k the threshold is stats target = 100).
Not sure if that will help here, though.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
not running PG 8.1 or later, it's really not worth your time
to test this. Multiprocessor scalability was hardly even on the radar
in 7.3 days.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
anagement of all
shared buffers; likewise for lockmgr
* lots of marginal tweaks such as paying attention to cache line
alignment of "hot" shared data structures
I'm probably forgetting some things but I think the bufmgr and lockmgr
changes were the biggest improveme
erential
may at root be that string comparison is way more expensive in the 8.2
installation, which again is possible if you went from C locale to some
other locale.
In short: check out "show lc_collate" in both installations.
regards, tom lane
ed_buffers large enough (or temp_buffers if
you're dealing with temp tables), everything will stay in memory anyway.
Don't sweat it.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our ex
both lc_collate and lc_ctype set to C, right? What about
database encoding?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
reason to repair the disk?
Probably best to make a dummy postgres-owned directory somewhere and
repoint the symlink at it, then DROP TABLESPACE.
CVS HEAD has recently been tweaked to be more forgiving of such cases...
regards, tom lane
---(end of
r-grade drives
are built to be beat upon a few hours a day, a few days a week, for the
length of their warranty period. Even if the warranties mention the
same number of years, there is a huge difference here.
regards, tom lane
---(end of broadcas
"Jonathan Ellis" <[EMAIL PROTECTED]> writes:
> I have a query hitting a view that takes 0.15s on 8.1 but 6s on 8.2.3:
> ...
> Is this a regression, or a "feature" of 8.2?
Hard to say without EXPLAIN ANALYZE output to compare.
oading it into
the new installation.
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
message can get through to the mailing list cleanly
ented on this, they expected to find one)
It seems hard to believe that the vendors themselves wouldn't burn in
the drives for half a day, if that's all it takes to eliminate a large
fraction of infant mortality. The savings in return processing and
customer goodwill would surely justify t
"Jonathan Ellis" <[EMAIL PROTECTED]> writes:
> On 4/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>>> Is this a regression, or a "feature" of 8.2?
>>
>> Hard to say without EXPLAIN ANALYZE output to compare.
> To my eye they are identica
Greg Smith <[EMAIL PROTECTED]> writes:
> On Fri, 6 Apr 2007, Tom Lane wrote:
>> It seems hard to believe that the vendors themselves wouldn't burn in
>> the drives for half a day, if that's all it takes to eliminate a large
>> fraction of infant mortality.
>
"Jonathan Ellis" <[EMAIL PROTECTED]> writes:
> On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Yeah, it sure is the same plan, and 8.2 seems to be a tad faster right
>> up to the hash join on user_id. Is user_id a textual datatype?
> user_id is an in
evaluated
partway up the join tree, but maybe that's wrong. You never did show
us the actual view definition ...
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
I think this is
preventing it from considering other join orders, too). If you change
the function's marking to be nonvolatile then the function disappears
from the plan entirely, and also it seems to prefer joining "clans" sooner.
regards, tom
rows in cd_id order.
I can see how to build one: make a variant of HashAggregate that returns
each input row immediately after hashing it, *if* it isn't a duplicate
of one already in the hash table. But it'd be a lot of work for what
seems a rather specialized need.
an DISTINCT; those
are separate code paths and will probably give you different plans.
But I don't think you'll find that GROUP BY does any better on this
particular measure of yielding rows before the full input has been
scanned.
regards, tom lane
tic/using-explain.html
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
ERE ...) as s
>WHERE gt.ctid=s.ctid;
> DROP INDEX gregs_table_ctid_idx;
> COMMIT;
Forget the index, it's useless here (hint: ctid is a physical address).
I'm wondering though why you don't just transpose the subquery's WHERE
condition into the DELETE'
eing the full details of the view
and tables. I'm wondering where the SubPlans are coming from, for instance.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
rows=1000 width=4)
I think you've left out some relevant details ... there's nothing
in what you said about a set-returning function ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
anslation_pair_id from translation_pair_data ...
If that's slow it's the topmost hash join's fault, else we have
to look at the UPDATE's side effects.
regards, tom lane
---(end of broadcast)---
ich
would let us eliminate them (or not) as the cause of the problem.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
le might help some, for large updates
like this. I've heard people claim that values as high as 64 are helpful.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
store TIDs of one table in another table, there
* doesn't seem to be enough use-case to justify adding a lot of code
* for that.
Maybe we should revisit that sometime, though I'm still not entirely
convinced by this example.
regards, tom lane
--
s the subquery
from being flattened into the upper query, which is what would have to
happen for a nestloop-with-inner-indexscan join to be considered.
AFAICS you've got to structure it so that the aggregation happens above
the join.
regards, tom lane
too bad...
Some day it'd be nice to be able to reorder grouping/aggregation steps
relative to joins, the way we can now reorder outer joins. Don't hold
your breath though ... I think it'll take some pretty major surgery on
the planner.
regards,
You do NOT want work_mem that high, at least not without an
extremely predictable, simple workload.
> wal_buffers = 512MB
I haven't heard any reports that there's a point in values even as high
as 1 meg for this.
regards, tom lane
---(
seen a case like that before.
Also, I assume the restriction on receipt date is very nonselective?
It doesn't seem to have changed the estimated rowcount much.
regards, tom lane
---(end of broadcast)---
TIP 3: Have yo
opclasses defined?
There's built-in support for reverse sort as of CVS HEAD, but in
existing releases you must have cobbled something together, and I wonder
if that could be a contributing factor ...
regards, tom lane
---(end of broadcast)
negate its result.
That's not relevant to your immediate problem, but if you've noticed
any strange behavior with your text_revop indexes, that's the reason...
regards, tom lane
---(end of broadcast)---
TIP 3
em familiar at all) or there's some additional contributing
factor. Steve, are you using any nondefault planner parameters?
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
ch since 8.2.3. So I'm a bit baffled. Maybe the misbehavior is
platform-specific ... what are you on exactly? Is there anything
nonstandard about your Postgres installation?
regards, tom lane
---(end of broadcast)---
T
Steve <[EMAIL PROTECTED]> writes:
> ... even if I force it to use the indexes
> (enable_seqscan=off) it doesn't make it any faster really :/
Does that change the plan, or do you still get a seqscan?
BTW, how big is this table really (how many rows)?
uld only take a second to run.
Please try this with both settings of enable_seqscan --- you don't need
to do "explain analyze" though, we just want to know which plan it picks
and what the cost estimate is.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
get it
confused.
I'm about to go out to dinner with the wife, but will have a closer
look when I get back, or tomorrow morning. We'll figure this out.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help s
ut what it is yet, though.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
:regclass from pg_index where indrelid =
'detail_summary'::regclass order by indexrelid;
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Steve <[EMAIL PROTECTED]> writes:
> On Thu, 12 Apr 2007, Tom Lane wrote:
>> I'm still not having any luck reproducing the failure here. Grasping at
>> straws again, I wonder if it's got something to do with the order in
>> which the planner examines the i
er of pages at each
> level of index, range of attribute values etc.)
I don't think what you're looking for is exposed anywhere. Postgres
doesn't rely on indexes for statistical information anyway; the
pg_statistic system catalog (see also pg_stats view)
seems more conservative,
as it's unlikely to break any cases that work well now, but on the other
hand it feels like plastering another wart atop a structure that's
already rather rickety.
Has anyone got any thoughts about the best way to do this?
regards, tom lan
ry about is what's the choice algorithm, not implementation
details.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ough to make it useful to use more than a few hundred meg.
(In fact, we never even bothered to fix the shared-memory-sizing
calculations to be able to deal with >2GB shared memory until 8.1;
if you try it in 8.0 it'll probably just crash.)
regards, tom lane
---
one: do exhaustive
search if there are less than N relevant indexes, for some N.
But that's not going to help Steve; we still need a smarter heuristic
for what to look for above the cutoff.
regards, tom lane
---(end of broadcast)--
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Has anyone got any thoughts about the best way to do this?
> How about doing both: sort the index by index scan cost; then pick the
> first index on the list and start adding indexes when they lower the
>
ANALYZE
should be modern enough IIRC). Exactly which PG release are you
running? Can you provide a self-contained test case?
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL proje
50-3395 in
src/backend/commands/trigger.c. Or do you see something broken there?
It works for me in a quick test.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
p, but there are no relevant changes since 8.1.0 as far
as I can see in the CVS logs.
What is that non-FK trigger shown in your results?
>Trigger posts_tsvectorupdate: time=61.659 calls=5
Could it possibly be firing an extra update on the table?
regards, tom lane
context.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
g00374.php
Those are good places to look if a discussion on -bugs or other lists
seems to tail off...
regards, tom lane
PS: the reason I couldn't reproduce the behavior was just that the dummy
data I was using didn't have the right statistics.
the release notes:
http://developer.postgresql.org/pgdocs/postgres/release.html
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
ng to be helpful at all. Jeff's idea
of using six single-column indexes with the above query might work,
though.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL pr
et random_page_cost
to 1 and inflate all the cpu_xxx cost constants by 10.)
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
mergejoinable equality operators --- but it's not explicitly
looking for duplicate conditions, rather this is falling out of a new
method for making transitive equality deductions.
regards, tom lane
---(end of broadcast)---
the join plan.
It's giving you the "right" answer for entirely the wrong reason.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
tably these
two threads:
http://archives.postgresql.org/pgsql-hackers/2007-01/msg00568.php
http://archives.postgresql.org/pgsql-hackers/2007-01/msg00826.php
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usene
not for this particular merchant. What exactly
is the relationship between status and merchant_id, anyway?
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
to do
with the subquery structure of your query. Were you showing us the
whole truth about your query, or were there details you left out?
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, plea
updated in more than six years.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
bly recent sub-release within your branch.
Read the release notes, and consider what you will say if one of the
several data-loss-causing bugs that were fixed long ago eats your DB:
http://developer.postgresql.org/pgdocs/postgres/release.html
regards, tom lane
---
zbuffer" in XLogFileInit to
maybe 16*XLOG_BLCKSZ, re-initdb, and see if performance improves.
The suggestion to use ftruncate is so full of holes that I won't
bother to point them all out, but certainly we could write more than
just XLOG_BLCKSZ at a time while prep
ide any
> option to do so.
We more or less do that already by filling the entire file in one go
when it's created ...
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by
certainly can't guess.
Also, there is an extremely good reason why Postgres will never be set
up to try to take over the whole machine by default: most of the
developers run multiple postmasters on their machines.
regards, tom lane
---(end
Carlos Moreno <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> But
>> the fundamental problem remains that we don't know that much about
>> how the installation will be used.
> Notice that the second part of my suggestion covers this --- have
> addi
r the disk write
> will take, especially when it requires finding free sectors to write to.
What's your point? If you're not going to put more than 128 characters
in the field, there's no difference in the amount of data involved.
regar
hat
varchar(N) adds an insert-time check on the length of the field value
--- but this is just a constraint check and doesn't have any direct
influence on how the value is stored.
regards, tom lane
---(end of broadcast)
formance problems seem "bursty" then you may also need to look
at adjusting bgwriter and/or vacuum cost delay parameters to smooth out
the I/O load.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/read
rts on the client
side, you could try successive queries like this with larger and larger
windows until you get an answer.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
end.
Try 8.2.
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
message can get through to the mailing list cleanly
sing a forced sort frequently *is* faster than a full-table
indexscan. It all depends on how much locality of reference there is,
ie how well the index order and physical table order match up. The
planner's statistical correlation estimate and cost parameters may be
far enough off to make it
Paul shows
SELECT tablename, attname, correlation FROM pg_stats where tablename='x';
x | a | 0.977819
x | b | 0.78292
when his initial verbal description indicated that b should have the
better correlation. So that's something else odd about this case.
t when there are enough tuples involved to
make the problem interesting.
So the bottom line is that the use-case for this optimization seems
far too narrow to justify the implementation effort.
regards, tom lane
---(end of broadcas
want commercial
support for your OS, a Centos->RHEL update will get you there easily.
AFAIK Red Hat doesn't have a clean solution for someone running Fedora
who suddenly realizes he needs a 24x7-supportable OS right now.
Something to work on... )
regards, tom lan
stencies from the 7.3 behavior
you're used to) or create a varchar_pattern_ops index on the column(s)
you're using LIKE with.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL projec
your breath
waiting for a fix ...
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
message can ge
701 - 800 of 4389 matches
Mail list logo
