Nevermind the reply, blonde moment on the ordering...This works :)ThanksOn 2/18/06, Adam Alkins <
[EMAIL PROTECTED]> wrote:Unfortunately I'm using 8.0.4 and this is for a government website, I only get so many maintenance windows. Is this the only workaround for this issue?
I did make a test index
Unfortunately I'm using 8.0.4 and this is for a government website, I only get so many maintenance windows. Is this the only workaround for this issue?I did make a test index as you described on my test box and tried the query and it used the new index. However, ORDER BY forum_id then last_post_tim
Adam Alkins <[EMAIL PROTECTED]> writes:
> SELECT t.topic_id
> FROM phpbb_topics AS t
> WHERE t.forum_id = 71
> AND t.topic_id NOT IN (205026, 29046,
> 144569, 59780, 187424,
> 138635, 184973, 170551, 22419, 1
On 2/17/06, Ragnar <[EMAIL PROTECTED]> wrote:
> Say again ?
> Let us say you have 1 billion rows, where the
> column in question contains strings like
> baaaaaa
> baaaaab
> baaaaac
> ...
> not necessarily in this order on disc of course
>
> The minimu
Hi!
I have a question about the query optimizer and the function scan. See
the next case:
CREATE TABLE a (id SERIAL PRIMARY KEY, userid INT4, col TEXT);
CREATE TABLE b (id SERIAL PRIMARY KEY, userid INT4, a_id INT4 REFERENCES
a (id), col TEXT);
CREATE INDEX idx_a_uid ON a(userid);
CREATE I
Does anybody know if it is possible to use the statistics collected by
PostgreSQL to do the following, and how?
- view all locks held by a particular PostgreSQL session (including how to
determine
the session ID#)
- determine effect of lock contention on overall database performance, as
well as
Indexing the t_name.name field, I can increase speed, but only if I
restrict my search to something like :
select *
from t_name
where t_name.name like 'my_search%'
(In this case it takes generally less than 1 second)
My question : Are there algorithms or tools that can speed up such a
type o
Title: RE: [PERFORM] SQL Function Performance
> in my case; both direct query and sql function gererate same execution plan. Also, execution plan belongs to the sql function better than direct sql > query plan. But, direct sql result comes less than 1 second. sql function result comes about i
Title: RE: [PERFORM] SQL Function Performance
I've run into this issue. It basically comes down to the plan that is being used inside the function is not the same as the plan used when you issue the query manually outside of the function. Although I'm no expert on when plans are prepared and
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Markus Schaber
> Sent: Thursday, February 16, 2006 5:45 AM
> To: pgsql-performance@postgresql.org; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] qsort again (was Re: [PERFORM] Stra
Hi List,
I would like some insight from the experts here as to how I can alter
which index PostgreSQL is choosing to run a query.
First off, I'm running an active web forum (phpBB) with sometimes
hundreds of concurrent users. The query in question is one which pulls
the lists of topics in the for
Title: Future of Table Partitioning
Hi,
I was wondering what the plan is for the future of table partitioning in PostgresQL. It is pretty hard for me to implement partitioning right now with its current limitation, specifically the fact that unique constraints cannot be enforced across part
Last night I implemented a non-recursive introsort in C... let me test it a bit more and then I'll post it here for everyone else to try out.On 2/16/06, Markus Schaber
<[EMAIL PROTECTED]> wrote:Hi, Ron,
Ron wrote:> ...and of course if you know enough about the data to be sorted so as to> constrain
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Wednesday, February 15, 2006 5:22 PM
> To: Ron
> Cc: pgsql-performance@postgresql.org; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] qsort again (was Re: [PERFOR
Good morning,
I've increased sort_mem until 2Go !!
and the error "out of memory" appears again.
Here the request I try to pass with her explain plan,
Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
-> Subquery Scan "day" (cost=2451676.23..2451688.73 rows=1000 width=16)
Mark Lewis <[EMAIL PROTECTED]> writes:
> I think we're actually on the same page here; you're right that the
> constraint above ( f(a)==f(b) iff a==b ) can't be extended to data types
> with more than 32 bits of value space. But the constraint I listed was
> actually:
> if a==b then f(a)==f(b)
I
On fös, 2006-02-17 at 08:01 -0500, Ron wrote:
> At 04:24 AM 2/17/2006, Ragnar wrote:
> >On fös, 2006-02-17 at 01:20 -0500, Ron wrote:
> > >
> > > OK, so here's _a_ way (there are others) to obtain a mapping such that
> > > if a < b then f(a) < f (b) and
> > > if a == b then f(a) == f(b)
> >
> >
On Thu, 2006-02-16 at 21:33 -0800, David Lang wrote:
> > In SQL_ASCII, just take the first 4 characters (or 8, if using a 64-bit
> > sortKey as elsewhere suggested). The sorting key doesn't need to be a
> > one-to-one mapping.
>
> that would violate your second contraint ( f(a)==f(b) iff (a==b) )
Ron wrote:
I assume we have such?
You could look at the Sample Databases project on pgfoundry:
http://pgfoundry.org/projects/dbsamples/
Best Regards,
Michael Paesold
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
On Fri, 2006-02-17 at 10:51, Ron wrote:
> I assume we have such?
Depends on what you wanna do.
For transactional systems, look at some of the stuff OSDL has done.
For large geospatial type stuff, the government is a good source, like
www.usgs.gov or the fcc transmitter database.
There are other
I assume we have such?
Ron
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
At 10:53 AM 2/17/2006, Martijn van Oosterhout wrote:
On Fri, Feb 17, 2006 at 08:23:40AM -0500, Ron wrote:
> >For this mapping, you need a full table sort.
> One physical IO pass should be all that's needed. However, let's
> pretend you are correct and that we do need to sort the table to get
> t
On Fri, Feb 17, 2006 at 08:18:41AM -0800, Scott Lamb wrote:
> On Feb 16, 2006, at 2:17 PM, Mark Lewis wrote:
> >Data types which could probably provide a useful function for f
> >would be
> >int2, int4, oid, and possibly int8 and text (at least for SQL_ASCII).
>
> ...and with some work, floats (
On Feb 16, 2006, at 2:17 PM, Mark Lewis wrote:Data types which could probably provide a useful function for f would be int2, int4, oid, and possibly int8 and text (at least for SQL_ASCII). ...and with some work, floats (I think just the exponent would work, if nothing else). bytea. Probably just ab
On Fri, Feb 17, 2006 at 08:23:40AM -0500, Ron wrote:
> >For this mapping, you need a full table sort.
> One physical IO pass should be all that's needed. However, let's
> pretend you are correct and that we do need to sort the table to get
> the key mapping. Even so, we would only need to do it
Selon Tom Lane <[EMAIL PROTECTED]>:
> [EMAIL PROTECTED] writes:
> > In fact, I don't know how to have explain plan of remote node.
>
> You send it an EXPLAIN.
Please, Could you send me what to put at end of request :
select * from dblink('my_connexion', 'EXPLAIN select * from test where
number
[EMAIL PROTECTED] writes:
> In fact, I don't know how to have explain plan of remote node.
You send it an EXPLAIN.
You can *not* use a view defined as you suggest if you want decent
performance --- the dblink functions will fetch the entire table
contents and the filtering will be done locally.
At 05:19 AM 2/17/2006, Markus Schaber wrote:
Hi, Ron,
Ron schrieb:
> OK, so here's _a_ way (there are others) to obtain a mapping such that
> if a < b then f(a) < f (b) and
> if a == b then f(a) == f(b)
>
> Pretend each row is a integer of row size (so a 2KB row becomes a 16Kb
> integer; a 4K
At 04:24 AM 2/17/2006, Ragnar wrote:
On fös, 2006-02-17 at 01:20 -0500, Ron wrote:
>
> OK, so here's _a_ way (there are others) to obtain a mapping such that
> if a < b then f(a) < f (b) and
> if a == b then f(a) == f(b)
> By scanning the table once, we can map say 001h (Hex used to ease
Hello,
I want to split table partitioned across two servers postgres (two hosts).
To query this remote object, I want to make view with union on two servers with
two dblink.
But, How to be sure that optimizer plan on remote node is same than local node
(ie : optimizer scan only the selected parti
Has anybody got some profiler data on the amount of time spent in
comparisons during a sort ? Say, the proposals here would give the most
gains on simple types like INTEGER ; so it would be interesting to know
how much time is now spent in comparisons for sorting a column of ints. If
it'
Hi, Ron,
Ron schrieb:
> OK, so here's _a_ way (there are others) to obtain a mapping such that
> if a < b then f(a) < f (b) and
> if a == b then f(a) == f(b)
>
> Pretend each row is a integer of row size (so a 2KB row becomes a 16Kb
> integer; a 4KB row becomes a 32Kb integer; etc)
> Since eve
Hi, David,
David Lang schrieb:
>> In SQL_ASCII, just take the first 4 characters (or 8, if using a 64-bit
>> sortKey as elsewhere suggested). The sorting key doesn't need to be a
>> one-to-one mapping.
> that would violate your second contraint ( f(a)==f(b) iff (a==b) )
no, it doesn't.
When b
On fös, 2006-02-17 at 01:20 -0500, Ron wrote:
> At 01:47 PM 2/16/2006, Ron wrote:
> >At 12:19 PM 2/16/2006, Scott Lamb wrote:
> >>On Feb 16, 2006, at 8:32 AM, Ron wrote:
> >>>Let's pretend that we have the typical DB table where rows are
> >>>~2-4KB apiece. 1TB of storage will let us have 256M-512
34 matches
Mail list logo
