> you have a two part part key on facility(country code, postal code),
> right?
Well, I'm glad you pointed it out, because I THOUGhT I had created it, but
apparently I haven't -- I only noticed that it was missing after I listed
all the other indexes. Looks like this query is one of the victims
Sorry, I didn'tpoint it out because an earlier post included the query with
documentation - that post got lost... or at least *I* can't see it.
The other half of the union renders the facilities that DO have addresses,
and because of the performance problem (which I have finally sorted out by
c
On Monday 16 October 2006 16:37, Carlo Stonebanks wrote:
> The facility_address_id is null statement is necessary, as this is a
> sub-query from a union clause and I want to optimise the query with
> the original logic intact. The value is not hard coded to true but
> rather to null.
Heh, you ne
>I think there's 2 things that would help this case. First, partition on
> country. You can either do this on a table level or on an index level
> by putting where clauses on the indexes (index method would be the
> fastest one to test, since it's just new indexes). That should shrink
> the size of
> what is the facility_address_id is null all about? remove it since you
> hardcode it to true in select.
The facility_address_id is null statement is necessary, as this is a
sub-query from a union clause and I want to optimise the query with the
original logic intact. The value is not hard code
On 10/15/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
that contains full address data
*/
select
f.facility_id,
null as facility_address_id,
null as address_id,
f.facility_type_code,
f.name,
null as address,
f.default_city as city,
f.default_state_code as state_c
I think there's 2 things that would help this case. First, partition on
country. You can either do this on a table level or on an index level
by putting where clauses on the indexes (index method would be the
fastest one to test, since it's just new indexes). That should shrink
the size of that ind
Magnus,
That shows that you don't really know how the memory manager in NT+
works ;-) *ALL* normal file I/O is handled through the memory manager
:-) So yes, they are both different access methods to the memory
manager, really.
"don't really" is a overstatement, I do not know at all how the m
Csaba Nagy wrote:
2d) Hints will damage the ongoing development of the optimizer by
reducing or eliminating test cases for its improvement.
You have no evidence for this.
My evidence (which I think I've mentioned in a couple of previous
postings), is the experience with the optimizer of tha
David,
For example, if you have 1G of RAM on the box, you can't
configure a cache of 900 meg and expect things to work well.
This is because the OS and associated other stuff running on
the box will use ~300megs. The system will page as a result.
Overcommitting of memory leads to trashing, yes
> Can you try temporarily disabling bitmap scans and see what comes up?
Well, that's slowing everything down. I've got a couple of results, below
1) Bitmap scan off, but seq scan enabled.
2) Bitmap scan and seq scan off
3) Bitmap scan back on, seq scan back on, and a new index created
4) VACUUM V
On Monday 16 October 2006 10:36, Brian Hurt wrote:
> ... Therefor, any hints feature *will* be used widely
> and in "inappropriate" circumstances. Protestations that
> this wasn't what the feature was meant for will fall on
> deaf ears.
I don't really care about this topic, as I've used Oracle
Brian Hurt wrote:
> Or, in shorter forms:
> 1) If you make it convient to use, expect it to be used a lot. If it
> shouldn't be used a lot, don't make it convient.
> 2) Breaking features means that people won't upgrade.
> 3) Programmers are idiots- design accordingly.
The PostgreSQL project has
I haven't weighed in on this because 1) I'm not a postgresql developer,
and am firmly of the opinion that they who are doing the work get to
decide how the work gets done (especially when you aren't paying them
for the work), and 2) I don't have any experience as a developer with
hints, and thu
On 10/15/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
Hi Merlin,
Well, I'm back. first of all, thanks for your dogged determination to help
me out - it is much appreciated. I owe you a beer or twelve.
The import has been running for a week. The import program got faster as I
tuned things. I
> 2d) Hints will damage the ongoing development of the optimizer by
> reducing or eliminating test cases for its improvement.
You have no evidence for this. The mindset of the postgres community you
cite further below usually mandates that you say things if you have
evidence for them... and this
really makes me think that that area is just a comfortable way to
access files on disk as memory areas; with the hope of propably better
caching then not-memory-mapped files.
No, absolutely not. CreateFileMaping() does much the same thing
as mmap() in Unix.
That would explain my disturbing i
I learned the hard way that just rising it can lead to a hard
performance loss :)
I looked back in the list archives to try to find your post on the
underlying problem, but could only find this rather terse sentence.
If you have more detailed information please post or point me at it.
But...m
Craig A. James wrote:
2. Hints
a) On a aesthetic/theoretical level, hints suck. They're ugly and rude
b) On a practical level, introducing hints will cause short- and
long-term problems
c) Hints would help DBAs solve urgent problems for which there is no
other solution
Pretty good su
> > > "anonymous mapped memory" site:microsoft.com turns out 0 (zero)
> > > results. And even splitting it up there seems to be nearly no
> > > information ... is the same thing by any chance also known by
> > > different names?
> >
> > Hmm. Yeah, most likely :) I may have grabbed that name fro
Magnus,
> "anonymous mapped memory" site:microsoft.com
> turns out 0 (zero) results. And even splitting it up there
> seems to be nearly no information ... is the same thing by
> any chance also known by different names?
Hmm. Yeah, most likely :) I may have grabbed that name from something
els
> > So: has anybody a hint how I can check how much shared_memory
> > is really used by PostgreSQL on Windows, to fine tune
> this parameter?
> >
> > I learned the hard way that just rising it can lead to a hard
> > performance loss :)
>
> Not really sur
Magnus,> So: has anybody a hint how I can check how much shared_memory> is really used by PostgreSQL on Windows, to fine tune this parameter?
>> I learned the hard way that just rising it can lead to a hard> performance loss :)Not really sure :) We're talking about anonymous mapped memory, and Idon
On Sun, Oct 15, 2006 at 05:25:31PM -0700, Craig A. James wrote:
> So my question is: Is there any argument that can be made to persuade those
> of you who are volunteering your time on the optimizer to even consider a
> HINTS proposal? Has all this discussion changed your perspective on 2(c),
>
> Hello,
>
> Shridhar Daithankar and Josh Berkus write on
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>
> shared_memory
>
> """
> There is one way to decide what is best for you. Set a high
> value of this parameter and run the database for typical
> usage. Watch usage of
Hello,
Shridhar Daithankar and Josh Berkus write on
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
shared_memory
"""
There is one way to decide what is best for you. Set a high value of
this parameter and run the database for typical usage. Watch usage of
shared memory using ipc
26 matches
Mail list logo
