"Karen Hill" <[EMAIL PROTECTED]> writes:
> On Feb 5, 9:33 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
>> I think you have omitted a bunch of relevant facts.
> The postgres version is 8.2.1 on Windows. The pl/pgsql function is
> inserting to an updatable view (basically two tables).
> [ sketch of sch
Karen Hill wrote:
The postgres version is 8.2.1 on Windows. The pl/pgsql function is
inserting to an updatable view (basically two tables).
CREATE TABLE foo1
(
) ;
CREATE TABLE foo2
(
);
CREATE VIEW viewfoo AS
(
);
CREATE RULE ruleFoo AS ON INSERT TO viewfoo DO INSTEAD
(
);
CREATE OR
On Feb 5, 9:33 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
> "Karen Hill" <[EMAIL PROTECTED]> writes:
> > I have a pl/pgsql function that is inserting 200,000 records for
> > testing purposes. What is the expected time frame for this operation
> > on a pc with 1/2 a gig of ram and a 7200 RPM disk?
>
>
What is your row size?
Have you checked to see what your current inserts per second are?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott
Marlowe
Sent: Tuesday, February 06, 2007 10:56 AM
To: Merlin Moncure
Cc: Karen Hill; pgsql-performance@postgre
what is the size of that index?
Have you considered breaking the index into components, ie more than one
index on the table?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Adam Rich
Sent: Tuesday, February 06, 2007 8:54 AM
To: 'Mark Stosberg'; pgsql-perf
Aqua data studio has a graphical explain built into it. It supports most
rdbms including postgres. Its what I use to performance tune DB2.
http://www.aquafold.com/
Index ANDing would suit you here
You have 3 tables with 3 possible indexes and it sounds like the query
is doing table scans where i
Tom Lane wrote:
>> I need the lovely index scan, but my table is hidden behind a view, and
>> all I get is the ugly sequential scan. Any ideas on how to convince the
>> optimizer to unfold the subquery properly?
>
> You should provide some context in this sort of gripe, like which PG
> version yo
On Tue, 2007-02-06 at 14:06 -0500, Merlin Moncure wrote:
> On 2/6/07, Mark Lewis <[EMAIL PROTECTED]> wrote:
> > > actually, I get the stupid award also because RI check to unindexed
> > > column is not possible :) (this haunts deletes, not inserts).
> >
> > Sure it's possible:
> >
> > CREATE TABLE
On 2/6/07, Mark Lewis <[EMAIL PROTECTED]> wrote:
> actually, I get the stupid award also because RI check to unindexed
> column is not possible :) (this haunts deletes, not inserts).
Sure it's possible:
CREATE TABLE parent (col1 int4);
-- insert many millions of rows into parent
CREATE TABLE c
On Tue, Feb 06, 2007 at 10:31:26 -0800,
Mark Lewis <[EMAIL PROTECTED]> wrote:
>
> Sure it's possible:
>
> CREATE TABLE parent (col1 int4);
> -- insert many millions of rows into parent
> CREATE TABLE child (col1 int4 REFERENCES parent(col1));
> -- insert many millions of rows into child, very
On Tue, 2007-02-06 at 12:01 -0500, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> > > On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > > > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > > > I have a p
On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > I have a pl/pgsql function that is inserting 200,000 records for
> > > testing purp
On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > I have a pl/pgsql function that is inserting 200,000 records for
> > > testing purposes. What is the expected time frame for this operat
On 2/6/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> I have a pl/pgsql function that is inserting 200,000 records for
> testing purposes. What is the expected time frame for this operation
> on a pc with 1/2 a gig of ram and a 7200 RPM disk? The
On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> I have a pl/pgsql function that is inserting 200,000 records for
> testing purposes. What is the expected time frame for this operation
> on a pc with 1/2 a gig of ram and a 7200 RPM disk? The processor is
> a 2ghz cpu. So far I've been sitting h
On Tue, Feb 06, 2007 at 09:39:54 -0500,
Mark Stosberg <[EMAIL PROTECTED]> wrote:
>
> I've been investigating partial indexes for the pets table. It has about
> 300,000 rows, but only about 10 are "active", and those are the ones we
> are care about. Queries are also frequently made on males vs f
Mark Stosberg wrote:
>
> I'm reading the explain analyze output correctly myself, nearly all of
> the time spent is related to the 'pets' table, but I can't see what to
> about it.
Something about typing that message jarred by brain to think to try:
VACUUM FULL pets;
VACUUM ANALYZE pets;
Now th
If I'm reading this correctly, 89% of the query time is spent
doing an index scan of earth_coords_idx. Scanning pets is only
taking 6% of the total time.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark
Stosberg
Sent: Tuesday, February 06, 2007 8:
Bruno Wolff III wrote:
>
> Some people here may be able to tell you more if you show us explain
> analyze output.
Here is my explain analyze output. Some brief context of what's going
on. The goal is to find "Pets Near You".
We join the pets table on the shelters table to get a zipcode, and then
On 2/6/07, Mark Stosberg <[EMAIL PROTECTED]> wrote:
It's also notable that the units used are meters, not miles like
geo_distance(). That's what the magic number of "16093.44" is-- 10 miles
converted to meters.
When I benchmarked this query against the old geo_distance() variation,
it was about
Tuners,
allways be aware that results on Windows may be totally different!
My main customer is running PostgreSQL 8.1 on MINIMUM shared buffers
max_connections = 100#
shared_buffers = 200# min 16 or max_connections*2, 8KB each
I changed it to this value from the very low de
On Tue, 2007-02-06 at 01:35, Karen Hill wrote:
> [snip] So far I've been sitting here for about 2 million ms
> waiting for it to complete, and I'm not sure how many inserts postgres
> is doing per second.
One way is to run analyze verbose on the target table and see how many
pages it has, and then
22 matches
Mail list logo
