--- 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 cach
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 tha
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'
-- 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 back
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
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
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
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,
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
--
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:
> 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 uniq
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 t
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
>
[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 defin
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 advis
[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 mid
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 (
[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
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 a
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'e
[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 now it's gone :-( I thought reloadin
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
[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:
> [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 lan
[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 ignor
[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
lon
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
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
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").
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
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
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 make
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 backward-c
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/
-
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 wi
"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 i
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
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
>
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 it
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 als
40 matches
Mail list logo
