Ioannis Theoharis <[EMAIL PROTECTED]> writes:
> Can you explain me in more details what kind of optimization is missing in
> that case?
Uh, no I can't really. It was mentioned on the mailing list with regards to
UNION ALL specifically. I think it applied to inherited tables as well but I
wouldn't
> Peter, Tatsuo:
>
> would happen with SELECT queries that, through a function or some
> > other mechanism, updates data in the database? Would those need to be
> > passed to pgpool in some special way?
>
> Oh, yes, that reminds me. It would be helpful if pgPool accepted a control
> string ...
IMO the bottle neck is not WAL but table/index bloat. Lots of updates
on large tables will produce lots of dead tuples. Problem is, There'
is no effective way to reuse these dead tuples since VACUUM on huge
tables takes longer time. 8.0 adds new vacuum delay
paramters. Unfortunately this does not h
> Tatsuo,
>
> > Suppose table A gets updated on the master at time 00:00. Until 00:03
> > pgpool needs to send all queries regarding A to the master only. My
> > question is, how can pgpool know a query is related to A?
>
> Well, I'm a little late to head off tangental discussion about this, but
On Fri, 21 Jan 2005, Greg Stark wrote:
> There's also a slight performance hit because there's an optimization that the
> planner does normally for simple queries that isn't currently done for either
> UNION ALL or inherited tables. I think it's planned to fix that soon.
Can you explain me in m
Peter, Tatsuo:
would happen with SELECT queries that, through a function or some
> other mechanism, updates data in the database? Would those need to be
> passed to pgpool in some special way?
Oh, yes, that reminds me. It would be helpful if pgPool accepted a control
string ... perhaps one in
Tatsuo,
What would happen with SELECT queries that, through a function or some
other mechanism, updates data in the database? Would those need to be
passed to pgpool in some special way?
Thanks,
Peter Darley
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Beha
Randolf Richardson wrote:
> > The best DB platform is what they currently have, regardless of what
> > they have, unless there is a very compelling reason to switch.
> [sNip]
>
> Have you heard the saying "Nobody ever got fired for picking IBM?" It
> is one of those situations where if the
I have a query that thinks it's going to generate a huge number of rows,
when in fact it won't:
INSERT INTO page_log.rrs
( bucket_id, page_id,project_id,other,
hits,min_hits,max_hits,total_duration,min_duration,max_duration )
SELECT a.rrs_bucket_id, page_id,project_id,other
Randolf,
You probably won't want to hear this, but this decision likely has
nothing to do with brands, models, performance or applications.
You are up against a pro salesman who is likely very good at what he
does. Instead spewing all sorts of "facts" and statistics to your
client, the salesma
> >Technically, you can also set up a rule to do things on a select with
DO
> >ALSO. However putting update statements in there would be considered
(at
> >least by me) very bad form. Note that this is not a trigger because
it
> >does not operate at the row level [I know you knew that already :-)].
This is probably a lot easier than you would think. You say that your
DB will have lots of data, lots of updates and lots of reads.
Very likely the disk bottleneck is mostly index reads and writes, with
some critical WAL fsync() calls. In the grand scheme of things, the
actual data is likely
So if you perform a "select * from parent" then does postgres internally
create a union between all the child tables and return you the results
of that?
Basically, yes. Kind of.
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ
ken <[EMAIL PROTECTED]> writes:
> On Fri, 2005-01-21 at 08:14, Greg Stark wrote:
> > ken <[EMAIL PROTECTED]> writes:
> >
> > > >From my understanding, all the data for these columns in all the child
> > > tables will be stored in this one parent table
> >
> > No, all the data is stored in the c
On Fri, 2005-01-21 at 08:14, Greg Stark wrote:
> ken <[EMAIL PROTECTED]> writes:
>
> > >From my understanding, all the data for these columns in all the child
> > tables will be stored in this one parent table
>
> No, all the data is stored in the child table.
So if you perform a "select * from
Tatsuo,
> Suppose table A gets updated on the master at time 00:00. Until 00:03
> pgpool needs to send all queries regarding A to the master only. My
> question is, how can pgpool know a query is related to A?
Well, I'm a little late to head off tangental discussion about this, but
The syst
Yes, I wasn't really choosing my examples particularly carefully, but I
think the conclusion stands: pgpool (or anyone/thing except for the
server) cannot in general tell from the SQL it is handed by the client
whether an update will occur, nor which tables might be affected.
That's not to say
Robert Sanford wrote:
How do I profile a user-defined function so that I know which parts of the
function are the ones that are taking the biggest chunk of time?
When I run EXPLAIN on the queries within the function none of them show up
as onerous burdens to the performance. But when they are all o
How do I profile a user-defined function so that I know which parts of the
function are the ones that are taking the biggest chunk of time?
When I run EXPLAIN on the queries within the function none of them show up
as onerous burdens to the performance. But when they are all operating
together wit
> > Now I read all the posts and I have some answers.
> >
> > Yes, I have a web aplication. I HAVE to know exactly how many pages I have
> > and I have to allow the user to jump to a specific page(this is where I
> > used limit and offset). We have this feature and I cannot take it out.
I'm afra
ken <[EMAIL PROTECTED]> writes:
> >From my understanding, all the data for these columns in all the child
> tables will be stored in this one parent table
No, all the data is stored in the child table.
> and that, furthermore, there is a "hidden" column in the parent table called
> tableoid th
> Uhmmm no :) There is no such thing as a select trigger. The closest
you
> would get
> is a function that is called via select which could be detected by
> making sure
> you are prepending with a BEGIN or START Transaction. Thus yes pgPool
> can be made
> to do this.
Technically, you can also set
Hi,
On Fri, Jan 21, 2005 at 03:37:20PM +0100, Matteo Beccati wrote:
>
> CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
> ANALYZE super_foo ;
>
> CREATE TABLE sub_foo1 () INHERITS ( super_foo );
> CREATE TABLE sub_foo2 () INHERITS ( super_foo );
> >
> >Yes, thi
Joshua D. Drake wrote:
Matt Clark wrote:
Presumably it can't _ever_ know without being explicitly told, because
even for a plain SELECT there might be triggers involved that update
tables, or it might be a select of a stored proc, etc. So in the
general case, you can't assume that a select does
Matt Clark wrote:
Presumably it can't _ever_ know without being explicitly told, because
even for a plain SELECT there might be triggers involved that update
tables, or it might be a select of a stored proc, etc. So in the
general case, you can't assume that a select doesn't cause an update,
a
Hi,
CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
ANALYZE super_foo ;
CREATE TABLE sub_foo1 () INHERITS ( super_foo );
CREATE TABLE sub_foo2 () INHERITS ( super_foo );
Yes, this could be used instead of a view. But there is one thing
missing. You can't just insert into super_foo an
> Now I read all the posts and I have some answers.
>
> Yes, I have a web aplication.
> I HAVE to know exactly how many pages I have and I have to allow the
user
> to
> jump to a specific page(this is where I used limit and offset). We
have
> this
> feature and I cannot take it out.
If your worki
>> > Some people have been doing it using a union view. There isn't actually
>> > a partition feature.
>>
>> Actually, there is. If found this example on pgsql-performance:
>>
>> >> CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
>> >> ANALYZE super_foo ;
>> >>
>> >> CREATE TABLE
Hi,
On Fri, Jan 21, 2005 at 09:50:46AM +0100, Matt Casters wrote:
>
> > Some people have been doing it using a union view. There isn't actually
> > a partition feature.
>
> Actually, there is. If found this example on pgsql-performance:
>
> >> CREATE TABLE super_foo ( partition NUMERIC, bar
Ah no, I think both of you have mistaken me. The problem here is not
about partial indexes (not really anyway).
I do have a partial index with "WHERE purge_date IS NOT NULL", and my
query does contain "WHERE purge_date IS NOT NULL" as well. The problem
here is, that all rows (or almost all) have
Now I read all the posts and I have some answers.
Yes, I have a web aplication.
I HAVE to know exactly how many pages I have and I have to allow the user to
jump to a specific page(this is where I used limit and offset). We have this
feature and I cannot take it out.
> SELECT * FROM tab WHERE c
Presumably it can't _ever_ know without being explicitly told, because
even for a plain SELECT there might be triggers involved that update
tables, or it might be a select of a stored proc, etc. So in the
general case, you can't assume that a select doesn't cause an update,
and you can't be su
> On Thu, Jan 20, 2005 at 11:31:29 -0500,
> Alex Turner <[EMAIL PROTECTED]> wrote:
>> I am curious - I wasn't aware that postgresql supported partitioned tables,
>> Could someone point me to the docs on this.
>
> Some people have been doing it using a union view. There isn't actually
> a partiti
Wondering ...
>From a performance standpoint, is it a bad idea to use inheritance
simply as a tool for easy database building. That is for creating
tables that share the same columns but otherwise are unrelated.
For example, let's say I have the following set of columns that are
common to many o
> Tatsuo,
>
> > Yes. However it would be pretty easy to modify pgpool so that it could
> > cope with Slony-I. I.e.
> >
> > 1) pgpool does the load balance and sends query to Slony-I's slave and
> >master if the query is SELECT.
> >
> > 2) pgpool sends query only to the master if the query is o
35 matches
Mail list logo
