?version=7.3idoc=0file=explicit-joins.html
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
will be affected by caching.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Manfred Koizar [EMAIL PROTECTED] writes:
On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane [EMAIL PROTECTED]
wrote:
Try reducing random_page_cost
With index scan cost being more than 25 * seq scan cost, I guess that
- all other things held equal - even random_page_cost = 1 wouldn't
help.
Oh
,
if only so I can try to fix it in future releases, but without useful
information I'll just have to write this off as an unsubstantiated report.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Martin Foster [EMAIL PROTECTED] writes:
As a side note, would you recommend disabling
fsync for added performance?
Only if you are willing to sacrifice crash-safety in the name of speed.
regards, tom lane
---(end of broadcast
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
However, it looks to me like the subquery-scan-outside plan probably
is the faster one, on both my machine and yours. I get
Woah, that's pretty whacky. It seems like it ought to be way faster to do a
single sequential
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
of?
Only possible if you don't have the FSM parameters set high enough.
Infrequent vacuuming means you need more FSM space, btw.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives
on HPUX (with a SCSI disk) and Linux (with an IDE disk, and a different
filesystem). I didn't see too much difference between 'em. RAID might
alter the equation, or not.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe
analyze', the whole thing slows WAY down to where each run can take
10-15 minutes.
Could we see EXPLAIN ANALYZE for the deletion query in both the fast and
slow states?
regards, tom lane
---(end of broadcast)---
TIP 8: explain
.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
=?ISO-8859-1?Q?SZUCS_G=E1bor?= [EMAIL PROTECTED] writes:
do regex ops (~, ~*) use index scan in non-C locales? Is it worth to
convert LIKE to regex?
The locale issues are the same either way.
regards, tom lane
---(end of broadcast
the casting quite right? (For instance,
timestamp isn't timestamp with time zone ...)
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
contents. PG's WAL logic can recover lost file
contents, but we have no way to help out the filesystem if it's lost
metadata.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
that the table
knows about new indexes.
You do not need to ANALYZE to get the system to notice new indexes.
4. i wish i had a rename index command to rename _swap
to its original index name.
You can rename indexes as if they were tables.
regards, tom lane
the profile depends on statistical
sampling. I like to have about a minute of accumulated runtime before
trusting the results. Repeat the same query multiple times if needed.
regards, tom lane
---(end of broadcast)---
TIP 2: you can
. I'd
really like to see some profiling of the poor-performing
external-storage case, so we can figure out what's going on.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send
the trouble.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
to find out what works well and what features are needed.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Joe Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
Ah-hah, I've sussed it ... you didn't actually change the storage
representation. You wrote:
Yeah, I came to the same conclusion this morning (update longdna set dna
= dna || '';), but it still seems that the chunked table is very
Sebastien Lemieux [EMAIL PROTECTED] writes:
On Wed, 6 Aug 2003, Tom Lane wrote:
The idea here is to make sure that the planner's statistics reflect the
full state of the table, not the empty state. Otherwise it may pick
plans for the foreign key checks that are optimized for small tables.
I
scott.marlowe [EMAIL PROTECTED] writes:
On Wed, 6 Aug 2003, Tom Lane wrote:
One obvious question is whether you have your foreign keys set up
efficiently in the first place. As a rule, the referenced and
referencing columns should have identical datatypes and both should
be indexed. (PG
...)
regards, tom lane
---(end of broadcast)---
TIP 3: 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
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
.
Journalling file contents updates, as opposed to filesystem metadata,
should be redundant with what we do in WAL. So I'd recommend
journalling metadata only, if that option is available (and if Postgres
stuff is the only stuff on the disk...)
regards, tom lane
: 37383.02 ms
scott=#
So it looks like the external-storage optimization for substring() does
work as expected, once you get the data into the right format ...
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget
an update
to 7.3.4 would be a more profitable use of time.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
;
explain analyze ... query ...
If it finishes in a reasonable amount of time, send the explain output.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
SELECT performance one bit. It would only
affect transactions that modify the database.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
list. That's
what's preventing 7.3 from doing a good job.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL
, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
= varchar.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
sort_mem. Multiple aggregates take
more RAM to process in a hashtable style ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
the planner recognizes that the new
index matches the ORDER BY request). Then you should get a plan with
a much smaller cost coefficient for this step.
regards, tom lane
PS: does server_id really need to be NUMERIC? Why not integer, or at
worst bigint
condition has to use stable
or immutable functions. By marking oneshot_random() stable, you
essentially say that it's okay to evaluate it only once per query,
rather than once at each row.
regards, tom lane
---(end of broadcast
Rob Nagler [EMAIL PROTECTED] writes:
Are there plans for explicit hints to the planner?
Personally, I'm philosophically opposed to planner hints; see previous
discussions in the archives.
regards, tom lane
---(end of broadcast
) how many tuples a given WHERE predicate resolves
to.
Why is that better than our existing implementation of column statistics?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map
into a range query:
where begin_time ... AND begin_time 'infinity';
See the archives for more discussion.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
Will that increase any throughput?
Probably not...
Also, doesn't flash memory have a very limited lifetime in write cycles?
Using it as WAL, you'd wear it out PDQ.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you
business. See, eg, the thread starting here:
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
that.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
).
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
. But I'm slightly concerned about changing the
semantics of existing queries. If someone can produce proof that this
is allowed (or even better, required) by the SQL spec, it'd be easier...
regards, tom lane
---(end of broadcast
with this afternoon was
removing the int8-and-int4 comparison operators from pg_operator.
It works as far as making int8col = 42 do the right thing, but I'm
not sure yet about side-effects.
regards, tom lane
---(end of broadcast)---
TIP 4
that the disk throughput triples during
the checkpoint.
Hm, better make sure the log includes some indication of when
checkpoints happen.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
heard of
before ;-).)
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Mary Edie Meredith [EMAIL PROTECTED] writes:
Is there any option to remove the cost numbers from the plan so we can
just use diff to automate the plan comparisons?
No, but a few moments with sed or perl should get the job done for you.
regards, tom lane
to disk. The performance
differential may thus be coming at the expense of reliability. If you
run Postgres with fsync off, does the differential go away?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off
that. You could hit this even with ~100-column
tables if you try to select all columns from a join of two or more.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
promise that doing so wouldn't break things.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
a drag on performance.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
dunno whether the TPC rules allow for
significant manual rewriting of the given query.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
to cancel the query after 10 min.
Force use of the indexes is not always an answer to performance issues.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
not normally
much need for apps to be explicitly aware of the column type names.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
to see.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through
nothing, there's a configuration problem with the pg-perform mail
list or your subscription or something. Talk to Marc (scrappy at
hub.org) about identifying and fixing the issue.
regards, tom lane
---(end of broadcast
and user_account.user_role_id.
The trick is to apply those before joining any other tables.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
, but
there it is...
/digression
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
are a couple of orders
of magnitude smaller than they actually are. Certainly the estimated
sizes of the joins are way off :-(
If you did analyze, it might help to increase the statistics target and
re-analyze.
regards, tom lane
---(end of broadcast
it.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
the index in question?
I suspect it is true that REINDEX locks more than it needs to, but we
should tread carefully about loosening it.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
other transactions should be
included in your count.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
... and it would give the wrong answers. Unless the cache is somehow
snapshot-aware, so that it can know which other transactions should be
included in your count.
The cache is an ordinary table, with xid's on every row. I meant
Bruce Momjian [EMAIL PROTECTED] writes:
It doesn't seem complex enough for a separate TODO.detail item.
I thought it was, if only because it is so easy to think of wrong
implementations.
regards, tom lane
---(end of broadcast
not, even before you look at
the performance issues.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
I think that's not happening, conditionally or otherwise. The atomicity
problems alone are sufficient reason why not, even before you look at
the performance issues.
What are the atomicity problems of adding a create/expire xid
.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
are till it's
looked. Also, VACUUM is the data collector for the free space map,
and so it is also charged with finding out how much free space exists
on every page.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you
is
concerned, but the thing that's needed to make it really useful is
to prevent caching of seqscan-read pages in the kernel disk buffers.
I don't know any portable way to do that :-(
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
, and this isn't
counted in EXPLAIN's runtime measurement. There's no claim anywhere
that the results of such an evaluation would be saved for other plans.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list
.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
to get that query to use an index?
Coerce the constants to bigint, for starters. However, a query that is
selecting almost 10% of the table, as your example is, probably
*shouldn't* be using an index.
regards, tom lane
---(end of broadcast
the memory mapped
into your address space, it's hard to believe that it matters how you
got hold of it.
In any case, mmap doesn't have the semantics we need. See past
discussions.
regards, tom lane
---(end of broadcast
+ fi
fi
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Chris Faulkner [EMAIL PROTECTED] writes:
I am seeing this message in my logs.
bt_fixroot: not valid old root page
That's not good. I'd suggest reindexing that index.
regards, tom lane
---(end of broadcast)---
TIP 2: you
Josh Berkus [EMAIL PROTECTED] writes:
Unless there's a way to find it in the compiled source?
See pg_controldata.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
using about 1% of the FSM space (272 out of 2
page slots).
I jacked up the free space map=20
pages but this doesn't appear to be working.
You know you have to restart the postmaster to make those changes take
effect, right?
regards, tom lane
in this calculation ... which says to me that the
page loading is right around the expected btree loading of 2/3.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
that's been holding an open transaction
for a long time.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS. For that you need
more-invasive operations like VACUUM FULL or REINDEX.
regards, tom lane
---(end
.
So far, all we can do is guess on the basis of very incomplete
information. When you aren't even bothering to mention whether a vacuum
is FULL or not, I have to wonder whether I have any realistic picture of
what's going on.
regards, tom lane
---(end
there with an
unclosed BEGIN ...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
to make both the stats table and its index grow, though.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
disk
fetches are needed, ie, the tables are much larger than RAM. 1.0 would
be appropriate if all your tables are always fully cached in RAM (since
RAM has by definition no random-access penalty). In intermediate cases
you need to select intermediate values.
regards, tom
table name in different schemas.
It doesnt affect the insertion/updating of the tables, but when we do a
pg_dump -Da -t table_name db_name /exp/table_name.sql, it will not
do a proper backup/dump.
I'd wonder about whether you have the right pg_dump, too.
regards, tom
and see if it gets better.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Neil Conway [EMAIL PROTECTED] writes:
On Mon, 2003-10-27 at 12:56, Greg Stark wrote:
Neil Conway [EMAIL PROTECTED] writes:
Uh, what? Why would an int8 need to be dynamically allocated
repeatedly?
Perhaps I'm wrong, I'm extrapolating from a comment Tom Lane made that
profiling showed
for the version-1 function call protocol was to
allow the pass-by-value-or-by-ref nature of these datatypes to be hidden
from most of the code, with an eye to someday making this a
platform-specific choice.
regards, tom lane
---(end of broadcast
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
rethinking.)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
go away when they tested 7.4 instead of 7.3. For instance, there is
hash aggregation capability, which would probably solve the aggregate
query problem you were complaining about in
http://archives.postgresql.org/pgsql-performance/2003-10/msg00640.php
regards, tom lane
and timestamp
datatypes actually have a concept of infinity; for other types you
can fake it with a large positive value.)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
as forcing a particular join order, while 7.4 doesn't.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
is putting a tunable
delay into VACUUM's per-page loop (ie, sleep N milliseconds after each
heap page is processed, and probably each index page too). This might
be useless or it might be the 80/20 solution you want. Want to try it
and report back?
regards, tom lane
Chester Kustarz [EMAIL PROTECTED] writes:
it seems that you cannot run analyze inside a transaction:
You can in 7.3.* ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once
1 - 100 of 3915 matches
Mail list logo