Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Andrew Sullivan [EMAIL PROTECTED] writes:
I would say just set up a project on pgfoundry.
I agree, though I think in the long term we do need a more complete set of
operators and functions in core.
Considering
these are bogus. And that would be an interesting warning too if
they made it not fire when it's bogus. bleagh.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
something like this
algorithm does on a column-by-column basis rather than on a
character-by-character basis.
But can you see any way to adapt this to a disk-sort?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
is where the parser inserts a ColumnRef node?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
));
/* Process user attributes, with appropriate attno mapping */
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
quite true, but then that's the basic difficulty with any part of
plpgsql.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
(olddatum) and then passes the result to
SET_VARSIZE(newdatum,len) is quite common.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
the corresponding assembly file?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Do we want something like this which provides a PQescapeByteaParam for
escaping bytea strings before passing them as text-mode parameters in
PQexecParam?
Seems a lot easier and more efficient to just pass out-of-line
relation named foo^*.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so
doing the same thing here where
complicated regexps are even *less* likely and dollars as literals more.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Do we want something like this which provides a PQescapeByteaParam for
escaping bytea strings before passing them as text-mode parameters in
PQexecParam?
Seems a lot easier and more efficient to just pass out-of-line
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
When we checkpoint we write out all dirty buffers. But ISTM we don't really
need to write out buffers which are dirty but which have an LSN older than
the
previous checkpoint. Those represent buffers which were
At the least this sounds like an unfriendly error message:
postgres=# select 1 order by 1;
ERROR: could not find pathkey item to sort
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1
is the hard part.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
checkpoints but how many of those can you have? And extending the checkpoint
doesn't seem like much of a concern. On the other hand it wouldn't be hard to
check would it?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
2007/7/4, Bruce Momjian [EMAIL PROTECTED]:
The solution is to fix the bloat, not add a work-around.
The bloat is a direct consequence of performing DDL in the midst of an OLTP
transaction.
Hardly. It's
transactions would be about 15s which is not so much longer than the xmin
horizon of the 90th percentile response time of 2*5s.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched
though. Reads can't
be re-ordered as well as writes can, they happen synchronously as far as an
individual backend is concerned.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading
;
return (Datum) 0;
}
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining
, not PQescapeByteaConn which has to do an
+ * extra layer of quoting to make the result amenable to being directly
+ * inserted into a query. */
extern unsigned char *PQunescapeBytea(const unsigned char *strtext,
size_t *retbuflen);
--
Gregory Stark
EnterpriseDB http
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Should this case at heaptuple.c:1606 be turned into an elog() now that we
have
plan cache invalidation?
No, I think it's good as-is. Reading the column as null is the correct
behavior.
Isn't the correct
2) Replanning overhead
3) Security issues of doing DDL at run-time
4) Difficulty structuring code when multiple procedures need the same
temporary tables but the procedures may be called in different orders for
different jobs and need different sets of tables.
--
Gregory Stark
the delay settings to reduce the
unnecessary impact of vacuum. But that just leaves us back where we started.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Heikki Linnakangas [EMAIL PROTECTED] writes:
I'm still struggling to understand why and how bgwriter increases
performance.
Under what circumstances, what workload?
The only benefit I can see is that it moves
was looking for
a way to display the count of records matching a search followed by the ten
records on the page without re-executing the search.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have
imagine relfozenxid would be useful for these
tables anyways?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
varlenas and then have heaptuple.c understand how to convert them in place.
But that leads to a ton of memory management or page locking problems.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't
Machinery. To copy otherwise, or to republish, requires a fee
and/or specific permission.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
that hash to
bufmgr. By definition any datatypes we expect to find in the database must
already be in the catalog before we start.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
2. Page format conversion is WAL-logged as a complete page replacement
It seems we get that for free. By definition any modification to a page after
conversion will be the first
you might have a cron
job run vacuum (or vacuum analyze) on this table more frequently.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
before the next batch of inserts?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
wanted to you could write a plugin which set the stats domain
based on whatever criteria you want whether that's time-of-day, userid, load
on the system, etc.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
was a few days
ago and did puzzle me because it was the first time I was starting up the
postmaster after a reboot.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free
transaction. Imagine a busy OLTP system
running hundreds of transactions per second trying to use a temporary table
for intermediate results. Mixing DDL and DML is just as bad an idea behind the
scenes as it is for users.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
thought just to figure
out what we need to measure.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
pair.
That could be interesting for other purposes such as marking a single
transaction with a new stats_domain so you can look at the stats for just that
transaction.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
. And if you had a cache hit ratio of zero then you would find as much as
little as 50% of the buffers with usage_count0.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your
consider doing is raising
BM_MAX_USAGE_COUNT. That's effectively tuning the percentage of the lru chain
that we decide we try to keep clean.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have
',' grouping_set
;
grouping_set:
a_expr
| rollup_list
| cube_list
| grouping_sets_specification
| empty_grouping_set
;
empty_grouping_set: '(' ')'
;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end
Gregory Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
For the moment, lie about WITH's status in the table so it will still get
quoted --- this is because of the expectation that WITH will become reserved
when the SQL recursive-queries patch gets done.
Out of curiosity
Gregory Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
For the moment, lie about WITH's status in the table so it will still get
quoted --- this is because of the expectation that WITH will become reserved
when the SQL recursive-queries patch gets done.
Out of curiosity
Andrew Dunstan [EMAIL PROTECTED] writes:
Gregory Stark wrote:
I'm thinking it may make sense to lie about all of these in quote_identifier
so that someone who upgrades from 8.2 to 8.3 can then upgrade to 8.4. If we
add reserved words in one step then there's no way to upgrade except
(*) from tab GROUP BY cube(a,b)
select a,b,count(*) from tab GROUP BY rollup(a,b)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
a cleverer idea.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
All that really has to happen is that dblink should by default not be
callable by any user other than Postgres.
Yeah, that is not an unreasonable change. Someone suggested it far
upthread, but we seem to have gotten
for share;
LOG: process 4631 still waiting for ShareLock on tuple (0,1) of relation 16423
of database 11408 after 5000.151 ms
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase
unknown to text
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
a user's point of view it's probably the
right thing. This is really what fsync=off should always have been doing.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
which sounds to be more like us saying, if we turn it
off
our transactions are bogus.
Hm, another possibility: synchronous_commit = off
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 7: You can
at your site of using
min always. But the question is if someone else wants to use m at their
site what problems does that cause you?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions
Peter Eisentraut [EMAIL PROTECTED] writes:
Am Donnerstag, 21. Juni 2007 00:38 schrieb Gregory Stark:
I think people are worried that an 'm' in one column might mean something
different than an 'm' in another column, and perhaps that is confusing.
To whom? the person writing
on your logic here? And why you disagree with my argument
that which abbreviations are correct is irrelevant in deciding whether we
should accept other abbreviations.
Afaict nobody has expressed a single downside to accepting other
abbreviations.
--
Gregory Stark
EnterpriseDB http
that is confusing.
To whom? the person writing it?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
there's usually going to be no savings at all. I just thought I would
get this down and in the mail archives before I forget it.
The OID trick doesn't work very well either.
expn OID trick?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end
in the varlena size.
Anything shorter than the shortest possible numeric representation can
implicitly be interpreted as some alternate compact representation. I already
had a patch that stored small integers in a single NumericDigit without any
numeric header at all.
--
Gregory Stark
EnterpriseDB
as an abbreviation for minutes *more* often than
min anyways. I see times written as 2h30m quite frequently and then there's
precedent like this:
$ time echo
real0m0.000s
user0m0.000s
sys 0m0.000s
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Tom Lane [EMAIL PROTECTED] writes:
It's case-sensitive. We had that argument already, but I still think
this decision was wrong.
I thought the consensus was that it should change.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end
a year ago.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
a bit of headroom too -- whereas previously we were failing at that
level on this hardware is a positive result as far as the TPCC benchmark
methodology.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
actually fit well but it's just a thought I had.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Greg Smith [EMAIL PROTECTED] writes:
On Fri, 15 Jun 2007, Gregory Stark wrote:
If I understand it right Greg Smith's concern is that in a busier system
where even *with* the load distributed checkpoint the i/o bandwidth demand
during t he checkpoint was *still* being pushed over 100
basically
wrappers that open the relation for you since that function requires it. The
real question is why you would ever have a tid without having a Relation
structure handy.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
+-+--+-+---+--+--+---
pg_catalog | currtid | tid | oid, tid| stark |
internal | currtid_byreloid | latest tid of a tuple
(1 row)
But how would it use currtid_byrelname?
--
Gregory Stark
EnterpriseDB http
tools would set the guc before issuing an EXPLAIN they planned
to parse.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
words.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
structure at all. On updates and
inserts it consults the indexam of the clustered index to ask if for a
suggested block. If the index's suggested block has enough free space then the
tuple is put there.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
-based.
I would suggest keeping the discussion on mail and including links to refer to
charts and tables in the wiki.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your
but you will get incorrect results. For example, returning different results
depending on whether the index or a full table scan is used.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting
Oleg Bartunov [EMAIL PROTECTED] writes:
On Thu, 14 Jun 2007, Gregory Stark wrote:
Am I correct to think of this like changing collations leaving your btree
index corrupt? In that case it probably won't cause any backend crash
either
but you will get incorrect results. For example
not sure it's a simple open and shut case
either.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, yes?
I think that actually is quite a nice effect. Certainly the loss of it is one
of the big practical disadvantages of using UUIDs over a sequence.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
is
very unlikely to be what the user expects.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
Throughput by no more than 20%.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
query for things like what query performed the
largest disk sort or what is the average cost/millisecond ratio or which
query nodes had the largest and smallest expected-rows/actual rows ratio etc.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
on pg_catalog.pg_am (cost=0.00..1.05 rows=1
width=4)
Filter: (amname = 'btree'::name)
(16 rows)
explain-with-schema-guc.patch.gz
Description: Binary data
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
(node.ms/node.cost)
FROM plan_table
GROUP BY node.type;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
of bytes required hold the constant. You seem to be
talking about the character length of strings.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project
the length for planning purposes)
would be to so a driver could size buffers appropriately. For example, in psql
where we use cursors to process rows, we might want to automatically use a
fetch count calculated to be large enough to receive approximately one
ethernet frame of data.
--
Gregory Stark
. How does this cause it to go slower?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Tom Lane [EMAIL PROTECTED] writes:
Trying the example in psql seems to be about the same speed both ways, with
if anything a slight advantage to select '1'.
Fwiw I see a slight advantage for '1' as well. I wonder why.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
quantities decimal aligned and so two records 1.00 and 0.01 will
take much more width than two records with 1.00 and 2.00.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase
be done using OpenGL already but I kind of doubt it.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
response time buying a faster
controller doesn't even help. It would shorten the duration of the checkpoint
but not eliminate it. A 30-second outage every half hour is just as
unacceptable as a 1-minute outage every half hour.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
immediately following a vacuum analyze we've been seeing?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Alvaro Herrera [EMAIL PROTECTED] writes:
Gregory Stark wrote:
is it possible it's related to the performance drop immediately
following a vacuum analyze we've been seeing?
I don't think so, unless you were counting on pgstats data of shared
tables for something. The optimizer, for one
Bruce Momjian [EMAIL PROTECTED] writes:
Gregory Stark wrote:
Is this still testing with all data fitting in RAM?
Yes. Having things out of RAM is going to make access even slower, but
it is going to allow the heap to be in RAM more often.
It would let us measure the actual impact
it shouldn't matter how long we wait, should it?
checkpoint_fsync_period = 30 # duration of the sync phase, in seconds
checkpoint_fsync_delay = 500 # max. delay between fsyncs
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end
Bruce Momjian [EMAIL PROTECTED] writes:
Well, it is summarized here:
http://momjian.us/expire/TOAST/SUMMARY.html
It made non-TOAST access 2x faster, but TOAST 7x slower, and that seemed
like a good compromise.
Is this still testing with all data fitting in RAM?
--
Gregory Stark
if that fails
looking for other types.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
says most of this and
more detailed comments in postgres.h.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
interested in.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Gregory Stark [EMAIL PROTECTED] writes:
Bruce Momjian [EMAIL PROTECTED] writes:
shared_buffers again was 32MB so all the data was in memory.
The case where all the data is in memory is simply not interesting. The cost
of TOAST is the random access seeks it causes. You seem
be invoking
hash_any() or mix() too.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
for a 2k chunk placed on a different page to swamp any effect the wasted
space would cause.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
size.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
of these first. Because if it shows
that bloating the toast table is faster than chopping up data into finer
chunks then we'll want to set TOAST_MAX_CHUNK_SIZE to 8k and then your tests
above will have to be rerun.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
.
Is there any way to tell, perhaps from the command string, that the process is
about to start exiting? What stage of exiting is it that we think the kernel
goes to lunch?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast
1101 - 1200 of 1641 matches
Mail list logo
