?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
at...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
bit looking for answers and tried all the
standard answers. It was still much much much slower.
Could we see the details? It's not very fair to not give us a chance to
learn about problems.
regards, tom lane
---(end of broadcast
helped.
I suspect he'd made up his mind already. Which is his privilege, but
it'd be nice to have some clue what the problem was ...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives
,
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
use? Would you show us EXPLAIN ANALYZE results when
using each index?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
due to out-of-date statistics ...
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
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
not quite sure
where the time is going in that case. (Hmmm... time to get out the
profiler...)
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe
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
hearing that
that adds a heck of a lot of overhead...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
the planner from choosing that alternative if there is any
other. The 1.00 is that artificial penalty.
We could probably hide this implementation detail from you if we tried
hard enough, but it hasn't bothered anyone enough to try.
regards, tom lane
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
inflating all the cpu_xxx costs by the same factor,
unless you have evidence that they are wrong in relation to each other.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http
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
disk block it's ever read.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
706101 :funcresulttype 23 :funcretset false
... (etc etc)
Would you do that and send it along? I'm curious ...
But i feel it can be lot more faster , can anyone suggest me something
to try.
Create an index on old_company_id, perhaps.
regards, tom lane
Rajesh Kumar Mallah [EMAIL PROTECTED] writes:
Tom Lane wrote:
Odd. Apparently the planner is picking a better plan in the function
context than in the subselect context --- which is strange since it
ought to have less information.
[ verbose plan snipped ]
Well, that sure seems
.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
.
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
, fecha_publicacion DESC)
AS ss
ORDER BY fecha_publicacion desc
LIMIT 10
OFFSET 0
See the weather reports example in the SELECT reference page for
motivation.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
...)
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
;
TRUNCATE table;
INSERT new data;
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.
regards, tom lane
.
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
of the bizarre-looking cost estimates. I don't
recommend setting it false as a system-wide setting. If you want
to nudge the planner towards indexscans, reducing random_page_cost
a little is probably a better way.
regards, tom lane
---(end
to the
filesystem change and how much to the hardware change. But I'd bet 20
disks on fibre array have way more net throughput than a single RAID
array on scsi.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner
;
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]
, the query will try to sort
_again_ _without_ index and thus lose all performance.
This is a limitation of the 7.3 query planner. 7.4 should do better.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your
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
could highlight nodes based on a large difference
between estimated and actual percentage, or just highlight the nodes
that are more than X percent of the runtime.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget
, 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
.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
= 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]
.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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:
Tom Lane writes:
The reason the planner does not much like this plan is that it's
estimating that quite a lot of rows will have to be hit in min_date_time
order before it finds enough rows with server_id = 21.
Very interesting. How does it know quite
reduces the actual cost of the indexscan (since
visiting the rows in index order becomes less of a random-access
proposition). We are aware that the planner doesn't model this effect
very well at present ...
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
Azlin Ghazali [EMAIL PROTECTED] writes:
Below is the exact statement I used:
That's not very informative. Could we see the results of EXPLAIN ANALYZE
on that SELECT? Also, what PG version are you running?
regards, tom lane
---(end of broadcast
.
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
ANALYZE to make the
same random sampling every time.
Also, it'd be a good idea to ANALYZE the needed tables by name,
explicitly, to ensure that they are analyzed in a known order
rather than whatever order ANALYZE happens to find them in pg_class.
regards, tom lane
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
is to find a representative dataset.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
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]
) into some code somewhere, but a patch that
approached it that way would be rejected as unmaintainable.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
not volunteering to try to code it up ...
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 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
result for a typical update
command?
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
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
can't do it alone.
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
, 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
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
?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
.
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
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
would probably tell something about that.
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
other transactions should be
included in your count.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
1 - 100 of 4083 matches
Mail list logo
