On 10/10/06, Jim C. Nasby [EMAIL PROTECTED] wrote:
Try w/o the explain analyze. It adds quite a bit of overhead and that
might be inconsistant between the systems (mainly it may have to do with
the gettimeofday() calls being implemented differently between Windows
and Linux..).
that was
All,
We are facing few issues while we install Postgres 8.0 in Windows 2000
Japanese OS. Installer kit name : postgresql-8.0-ja
Scenario 1: While installing PostGRE 8.0, we got an logon failure at the end
of installing the component telling that it failed to produce the process
for initdb and
On Mon, Oct 09, 2006 at 06:45:16PM -0400, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
One of the big problems with doing set enable_...=off is that there's no
way to embed that into something like a view, so you're almost forced
into putting into the application code itself,
On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote:
One of the big problems with doing set enable_...=off is that there's no
way to embed that into something like a view, so you're almost forced
into putting into the application code itself, which makes matters even
worse.
On Mon, Oct 09, 2006 at 08:22:39PM -0700, Joshua D. Drake wrote:
Imagine I got run over by a train, and someone was reading my code.
Which would be easier for them to maintain: Code with weird SQL, or code
with sensible, well-written SQL and explicit hints?
You forgot the most
Jim C. Nasby [EMAIL PROTECTED] writes:
I'd rather have the ugly solution sooner rather than the elegant one
later (if ever).
The trouble with that is that we couldn't ever get rid of it, and we'd
be stuck with backward-compatibility concerns with the first (over
simplified) design. It's
On Tue, Oct 10, 2006 at 09:07:03AM -0500, Jim C. Nasby wrote:
Would you put something from the obfuscated C contest
into production with comments describing what it does,
If nothing else, it would be a nice practical joke =)
/* Steinar */
--
Homepage: http://www.sesse.net/
On Tue, Oct 10, 2006 at 10:14:48AM -0400, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
I'd rather have the ugly solution sooner rather than the elegant one
later (if ever).
The trouble with that is that we couldn't ever get rid of it, and we'd
be stuck with
Jim C. Nasby wrote:
On Mon, Oct 09, 2006 at 03:41:09PM -0700, Joshua D. Drake wrote:
One of the big problems with doing set enable_...=off is that there's no
way to embed that into something like a view, so you're almost forced
into putting into the application code itself, which makes matters
Jim C. Nasby wrote:
On Mon, Oct 09, 2006 at 08:22:39PM -0700, Joshua D. Drake wrote:
Imagine I got run over by a train, and someone was reading my code.
Which would be easier for them to maintain: Code with weird SQL, or code
with sensible, well-written SQL and explicit hints?
You forgot the
Steinar H. Gunderson wrote:
On Tue, Oct 10, 2006 at 09:07:03AM -0500, Jim C. Nasby wrote:
Would you put something from the obfuscated C contest
into production with comments describing what it does,
If nothing else, it would be a nice practical joke =)
nice isn't the word I would use ;)
While doing a verbose vacuum, I'm constantly hitting things like:
DETAIL: 3606 dead row versions cannot be removed yet.
I believe this is a problem, because I still do have some empty tables
requireing up to 3-400 ms just to check if the table is empty (see
thread slow queue-like empty table).
Tobias Brox [EMAIL PROTECTED] writes:
If pg_stat_activity.query_start actually is the start time of the
transaction,
... but it isn't.
Is there any way to find the longest running transaction?
Look in pg_locks to see the lowest-numbered transaction ID --- each
transaction will be holding
Moving to -general.
On Tue, Oct 10, 2006 at 04:17:06PM +0530, Ravindran G - TLS, Chennai. wrote:
All,
We are facing few issues while we install Postgres 8.0 in Windows 2000
Japanese OS. Installer kit name : postgresql-8.0-ja
Is there a reason you're not using 8.1.4? 8.0 was the first
On Mon, Oct 09, 2006 at 23:33:03 +0200,
Tobias Brox [EMAIL PROTECTED] wrote:
Just a comment from the side line; can't the rough set
enable_seqscan=off be considered as sort of a hint anyway? There have
been situations where we've actually had to resort to such crud.
That only works for
[Tom Lane - Tue at 12:23:40PM -0400]
Look in pg_locks to see the lowest-numbered transaction ID --- each
transaction will be holding exclusive lock on its own XID. You can
correlate that back to pg_stat_activity via the PID.
Thanks a lot for the quick reply - I've already identified one
[Tobias Brox - Tue at 06:39:13PM +0200]
Thanks a lot for the quick reply - I've already identified one
long-running transaction.
belonging to autovacuum ... how come?
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore
Tobias Brox [EMAIL PROTECTED] writes:
[Tobias Brox - Tue at 06:39:13PM +0200]
Thanks a lot for the quick reply - I've already identified one
long-running transaction.
belonging to autovacuum ... how come?
Blocked on someone else's lock, maybe?
regards, tom lane
[Tom Lane - Tue at 12:42:52PM -0400]
belonging to autovacuum ... how come?
Blocked on someone else's lock, maybe?
hardly, the autovacuum is the only one having such a low transaction id,
and also the only one hanging around when waiting a bit and rechecking
the pg_locks table.
Tobias Brox [EMAIL PROTECTED] writes:
Blocked on someone else's lock, maybe?
hardly, the autovacuum is the only one having such a low transaction id,
and also the only one hanging around when waiting a bit and rechecking
the pg_locks table.
Hmph. Is the autovac process actually doing
Tobias Brox [EMAIL PROTECTED] writes:
[Tom Lane - Tue at 01:09:52PM -0400]
Hmph. Is the autovac process actually doing anything (strace would be
revealing)? If not, can you attach to the autovac process with gdb and
get a stack trace to see where it's blocked?
Sorry ... I SIGINT'ed it, and
Jim,
We've depricated things before, I'm sure we'll do it again. Yes, it's a
pain, but it's better than not having anything release after release.
And having a formal hint language would at least allow us to eventually
clean up some of these oddball cases, like the OFFSET 0 hack.
I'm also
[Tom Lane - Tue at 01:18:27PM -0400]
Hmph. Is the autovac process actually doing anything (strace would be
revealing)?
It's definitively doing something; mostly reading, but also some few
writes, semops and opens.
If not, can you attach to the autovac process with gdb and
get a stack
Tobias Brox [EMAIL PROTECTED] writes:
(gdb) bt
#0 0xb7c599f8 in select () from /lib/tls/libc.so.6
#1 0x08253c53 in pg_usleep ()
#2 0x0812ee93 in vacuum_delay_point ()
#3 0x0812f2a5 in lazy_vacuum_rel ()
#4 0x0812ef7b in lazy_vacuum_rel ()
#5 0x0812b4b6 in vac_update_relstats ()
That
[Tom Lane - Tue at 02:04:55PM -0400]
It seems stuck, has had the same transid for a long while, and the
number of undeletable dead rows in our tables are increasing.
Perhaps you have overly aggressive vacuum cost delay settings?
Perhaps, though I wouldn't expect it to sleep in the middle
Tobias Brox [EMAIL PROTECTED] writes:
Perhaps you have overly aggressive vacuum cost delay settings?
autovacuum_vacuum_cost_delay = 500
autovacuum_vacuum_cost_limit = 200
Well, that's going to cause it to sleep half a second after every dozen
or so page I/Os. I think you'd be well advised to
[Tom Lane - Tue at 02:26:53PM -0400]
autovacuum_vacuum_cost_delay = 500
autovacuum_vacuum_cost_limit = 200
Well, that's going to cause it to sleep half a second after every dozen
or so page I/Os. I think you'd be well advised to reduce the delay.
Modified it to 20/250, and it
On Tue, Oct 10, 2006 at 10:28:29AM -0700, Josh Berkus wrote:
Jim,
We've depricated things before, I'm sure we'll do it again. Yes, it's a
pain, but it's better than not having anything release after release.
And having a formal hint language would at least allow us to eventually
clean
I currently have a db supporting what is for the most part an OLAP data
warehousing application.
One table (good data) has roughly 120 million rows, divided into roughly
40 different relational groups (logically by foreign key). Every time I
add data to this table, I need to afterwards scrub
Brendan Curran [EMAIL PROTECTED] writes:
CREATE TEMP TABLE temp_list_suppress(email_record_id int8);
INSERT INTO temp_list_suppress
SELECT email_record_id from ONLY email_record er
WHERE email_list_id = 9 AND email IN
(select email from suppress);
CREATE INDEX
Tom Lane wrote:
Brendan Curran [EMAIL PROTECTED] writes:
CREATE TEMP TABLE temp_list_suppress(email_record_id int8);
INSERT INTO temp_list_suppress
SELECT email_record_id from ONLY email_record er
WHERE email_list_id = 9 AND email IN
(select email from suppress);
Brendan Curran [EMAIL PROTECTED] writes:
Tom Lane wrote:
Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
FIRST INSERT (Just the select is explained):
EXPLAIN ANALYZE, please, not just EXPLAIN.
regards, tom lane
---(end
Tom Lane wrote:
Brendan Curran [EMAIL PROTECTED] writes:
Tom Lane wrote:
Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
FIRST INSERT (Just the select is explained):
EXPLAIN ANALYZE, please, not just EXPLAIN.
regards, tom lane
Sorry,
Jim C. Nasby wrote:
(snippage)... but we'll never get any progress so long as every
time hints are brought up the response is that they're evil and should
never be in the database. I'll also say that a very simple hinting
language (ie: allowing you to specify access method for a table, and
join
Mark Kirkwood wrote:
who believe it is possible to build a start enough optimizer.
That's meant to read smart enough optimizer .. sorry.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On Tue, Oct 10, 2006 at 05:46:18PM -0600, Brendan Curran wrote:
Tom Lane wrote:
Brendan Curran [EMAIL PROTECTED] writes:
Tom Lane wrote:
Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
FIRST INSERT (Just the select is explained):
EXPLAIN ANALYZE, please, not
-- tom lane wrote -
Jim C. Nasby [EMAIL PROTECTED] writes:
I'd rather have the ugly solution sooner rather than the elegant one
later (if ever).
The trouble with that is that we couldn't ever get rid of it, and we'd
be stuck with
Brian Herlihy [EMAIL PROTECTED] writes:
What would it take for hints to be added to postgres?
A *whole lot* more thought and effort than has been expended on the
subject to date.
Personally I have no use for the idea of force the planner to do
exactly X given a query of exactly Y. You don't
Brendan Curran [EMAIL PROTECTED] writes:
So much time is being spent in the Unique and Sort leaves... I would
think that it wouldn't need to do the unique portion, since there is no
DISTINCT clause...
There's nothing in that query suggesting that suppress.email is unique.
If you know that it
--- Tom Lane [EMAIL PROTECTED] wrote:
Personally I have no use for the idea of force the planner to do
exactly X given a query of exactly Y. You don't have exactly Y
today, tomorrow, and the day after (if you do, you don't need a
hint mechanism at all, you need a mysql-style query cache).
I
40 matches
Mail list logo
