Why would postgres use a different query plan for declared cursors than
without?
I have a relatively simple query that takes about 150ms using explain
analyze. However, when I wrap the same query in a declared cursor
statement, the subsequent fetch statement takes almost 30seconds. For
some re
Christopher Browne wrote:
In the last exciting episode, [EMAIL PROTECTED] (Joe Conway) wrote:
That's exactly what we're doing, but using inherited tables instead of
a union view. With inheritance, there is no need to rebuild the view
each time a table is added or removed. Basically, in our applicat
Hi Joe,
You went to quite a bit of effort, thanks I have the picture now.
Using inheritence seems to be a useful refinement on top of the earlier
outlined aproach using the UNION ALL view with appropriate predicates on the
condition used to do the partitioning. Having the individual partitions
de
insert into X
select a.keyA,
a.keyB,
a.colA,
a.colB
from Y a left join X b
using (keyA, keyB)
where b.keyA is NULL and
b.keyB is NULL;
With the appropriate indexes, this is pretty fast but I think a merge
would be much faster.
Problem is it's subject to race
Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
and DB2's implementation of MERGE, which does what AMOUNTS to what is
described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
EXISTS).
No, you shouldn't iterate row-by-row through the temp table.
Whenever possibl
Simon Riggs wrote:
Jim C. Nasby
On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
PostgreSQL's functionality is in many ways similar to Oracle
Partitioning.
Loading up your data in many similar tables, then creating a view like:
CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
SELECT
On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> Hi,
>
> On Tue, 14 Sep 2004 22:10:04 -0700
> Steve Atkins <[EMAIL PROTECTED]> wrote:
>
> > > Is there by any chance a set of functions to manage adding and removing
> > > partitions? Certainly this can be done by hand, but having a
Iain wrote:
That's exactly what we're doing, but using inherited tables instead of a
union view. With inheritance, there is no need to rebuild the view each
time a table is added or removed. Basically, in our application, tables
are partitioned by either month or week, depending on the type of data
"J. Andrew Rogers" <[EMAIL PROTECTED]> writes:
> We do something very similar, also using table inheritance
I have a suspicion postgres's table inheritance will end up serving as a good
base for a partitioned table feature. Is it currently possible to query which
subtable a record came from thou
On Tue, 2004-09-14 at 21:30, Joe Conway wrote:
> That's exactly what we're doing, but using inherited tables instead of a
> union view. With inheritance, there is no need to rebuild the view each
> time a table is added or removed. Basically, in our application, tables
> are partitioned by eithe
Simon Riggs wrote:
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
I think
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
Best Regards, Simon Ri
Chris Browne <[EMAIL PROTECTED]> wrote on 15.09.2004, 04:34:53:
> [EMAIL PROTECTED] ("Simon Riggs") writes:
> > Well, its fairly straightforward to auto-generate the UNION ALL view,
and
> > important as well, since it needs to be re-specified each time a new
> > partition is loaded or an old one i
Jeffrey W. Baker wrote:
All these replies are really interesting, but the point is not that my
RAIDs are too slow, or that my CPUs are too slow. My point is that, for
long stretches of time, by database doesn't come anywhere near using the
capacity of the hardware. And I think that's odd and woul
Josh Berkus wrote:
- the use of inherited tables to partition this huge number of rows and
yet allow simple query access to it seems to work well, at least in
early validation tests
- had we simply taken the original database and "slammed" it into
Postgres with no further thought, we wou
Joe,
> - the use of inherited tables to partition this huge number of rows and
> yet allow simple query access to it seems to work well, at least in
> early validation tests
> - had we simply taken the original database and "slammed" it into
> Postgres with no further thought, we w
[EMAIL PROTECTED] wrote:
Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24:
We're not completely done with our data conversion (from a commercial
RDBMSi), but so far the results have been excellent. Similar to what
others have said in this thread, the conversion involved restructuring
Hi,
On Tue, 14 Sep 2004 22:10:04 -0700
Steve Atkins <[EMAIL PROTECTED]> wrote:
> > Is there by any chance a set of functions to manage adding and removing
> > partitions? Certainly this can be done by hand, but having a set of
> > tools would make life much easier. I just looked but didn't see an
Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24:
> Chris Browne wrote:
> > Might we set up the view as:
> >
> > create view combination_of_logs as
> > select * from table_1 where txn_date between 'this' and 'that'
> >union all
> > select * from table_2 where txn_date between
In the last exciting episode, [EMAIL PROTECTED] (Joe Conway) wrote:
> That's exactly what we're doing, but using inherited tables instead of
> a union view. With inheritance, there is no need to rebuild the view
> each time a table is added or removed. Basically, in our application,
> tables are pa
20 matches
Mail list logo
