Mark Lewis wrote:
> I imported my test dataset
> and was almost immediately able to track down the cause of my
> performance problem.
Why don't you tell us what the problem was :-) ?
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: if p
On Tue, Mar 29, 2005 at 01:48:48 -0700,
Karim A Nassar <[EMAIL PROTECTED]> wrote:
>
> For this FK check, there only need be one referring id to invalidate the
> delete. ISTM that for any delete with a FK reference, the index could
> always be used to search for a single value in the referring ta
On Mon, 28 Mar 2005, Stephan Szabo wrote:
> > On Mon, 28 Mar 2005, Simon Riggs wrote:
> > > run the EXPLAIN after doing
> > > SET enable_seqscan = off
...
> I think you have to prepare with enable_seqscan=off, because it
> effects how the query is planned and prepared.
orfs=# SET enable_seqsca
> Well, based upon the evidence so far, the Optimizer got it right:
Agreed. So, this means that the answer to my original question is "that
delete gonna take a long time"?
Seems that there is still something wrong. From what I can tell from
everyones questions, the FK constraint on measurement is
> Each value has 1/13th of the table, which is too many rows per value to
> make an IndexScan an efficient way of deleting rows from the table.
But, the original question was that the delete that was taking a long time
was on a different table. I tried to delete 150 rows from a table with 750
rows
On Tue, 2005-03-29 at 12:31 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > That implies to me that LIMIT queries are not considered correctly in
> > the M&L formula and thus we are more likely to calculate a too-high cost
> > for using an index in those circumstancesand th
Simon Riggs <[EMAIL PROTECTED]> writes:
> That implies to me that LIMIT queries are not considered correctly in
> the M&L formula and thus we are more likely to calculate a too-high cost
> for using an index in those circumstancesand thus more likely to
> SeqScan for medium sized relations?
Yo
On Tue, 2005-03-29 at 09:40 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > ...but, I see no way for OidFunctionCall8 to ever return an answer of
> > "always just 1 row, no matter how big the relation"...so tuples_fetched
> > is always proportional to the size of the relation.
On Tue, 29 Mar 2005, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > If there were some way to pass a "limit" into SPI_prepare that was treated
> > similarly to a LIMIT clause for planning purposes but didn't actually
> > change the output plan to only return that number of rows, w
On Tue, 29 Mar 2005, Simon Riggs wrote:
> On Tue, 2005-03-29 at 10:31 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > If action is NO ACTION or RESTRICT then
> > > we need to SELECT at most 1 row that matches the criteria
> > > which means we can use LIMIT 1
> >
> > >
On Tue, 2005-03-29 at 10:31 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > If action is NO ACTION or RESTRICT then
> > we need to SELECT at most 1 row that matches the criteria
> > which means we can use LIMIT 1
>
> > If action is CASCADE, SET NULL, SET DEFAULT then
On Tue, 2005-03-29 at 09:56 -0500, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > If there were some way to pass a "limit" into SPI_prepare that was treated
> > similarly to a LIMIT clause for planning purposes but didn't actually
> > change the output plan to only return that numb
Simon Riggs <[EMAIL PROTECTED]> writes:
> If action is NO ACTION or RESTRICT then
> we need to SELECT at most 1 row that matches the criteria
> which means we can use LIMIT 1
> If action is CASCADE, SET NULL, SET DEFAULT then
> we need to UPDATE or DELETE all rows that match the
On Tue, 2005-03-29 at 05:50 -0800, Stephan Szabo wrote:
> On Tue, 29 Mar 2005, Simon Riggs wrote:
>
> > On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > > Each value has 1/13th of the table, which is too many rows per value to
> > > > make an IndexScan an efficient way of deleting ro
Stephan Szabo <[EMAIL PROTECTED]> writes:
> If there were some way to pass a "limit" into SPI_prepare that was treated
> similarly to a LIMIT clause for planning purposes but didn't actually
> change the output plan to only return that number of rows, we could use
> that.
Hmm ... the planner does
Simon Riggs <[EMAIL PROTECTED]> writes:
> ...but, I see no way for OidFunctionCall8 to ever return an answer of
> "always just 1 row, no matter how big the relation"...so tuples_fetched
> is always proportional to the size of the relation. Are unique indexes
> treated just as very-low-selectivity i
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 29 Mar 2005, Simon Riggs wrote:
>> The SQL generated for RI checking by the RI triggers currently applies a
>> limit at execution time, not at prepare time. i.e. there is no LIMIT
>> clause in the SQL.
>>
>> We know whether the check will be limi
On Tue, 29 Mar 2005, Stephan Szabo wrote:
> On Tue, 29 Mar 2005, Simon Riggs wrote:
>
> > On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > > Each value has 1/13th of the table, which is too many rows per value to
> > > > make an IndexScan an efficient way of deleting rows from the ta
On Tue, 29 Mar 2005, Simon Riggs wrote:
> On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > Each value has 1/13th of the table, which is too many rows per value to
> > > make an IndexScan an efficient way of deleting rows from the table.
> >
> > But, the original question was that the
On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > Each value has 1/13th of the table, which is too many rows per value to
> > make an IndexScan an efficient way of deleting rows from the table.
>
> But, the original question was that the delete that was taking a long time
> was on a dif
On Fri, 2005-03-25 at 09:38 -0800, Josh Berkus wrote:
> > I guess I was wondering if there is other general tuning advice for such
> > large table indexes such as increasing statistics, etc.
>
> If you're going with the drop/load/recreate option, then I'd suggest
> increasing work_mem for the du
On Mon, 2005-03-28 at 13:03 -0700, Karim A Nassar wrote:
> > Well, based upon the evidence so far, the Optimizer got it right:
>
> Agreed. So, this means that the answer to my original question is "that
> delete gonna take a long time"?
>
> Seems that there is still something wrong. From what I c
On Mon, 2005-03-28 at 09:37 -0700, Karim A Nassar wrote:
> On Mon, 28 Mar 2005, Stephan Szabo wrote:
> > > On Mon, 28 Mar 2005, Simon Riggs wrote:
> > > > run the EXPLAIN after doing
> > > > SET enable_seqscan = off
>
> ...
>
> > I think you have to prepare with enable_seqscan=off, becaus
Tom Lane Wrote:
> Hmm ... I wonder how hard it would be to teach EXPLAIN ANALYZE to show
> the runtime expended in each trigger when the statement is of a kind
> that has triggers. We couldn't break down the time *within* the
> triggers, but even this info would help a lot in terms of finger
> poi
On Mon, 28 Mar 2005, Karim A Nassar wrote:
> On Mon, 28 Mar 2005, Simon Riggs wrote:
> > run the EXPLAIN after doing
> > SET enable_seqscan = off
>
> The results I previously supplied were searching for a non-existent
> value, so I have provided output for both cases.
>
> ***
> *** Searching f
On Sun, 2005-03-27 at 07:05 -0800, Stephan Szabo wrote:
> On Sat, 26 Mar 2005, Karim Nassar wrote:
> > Some improvement. Even better once it's cached. Row estimate didn't
> > change. Is this the best I can expect? Is there any other optimizations
> > I am missing?
>
> I'm not sure, really. Running
On Thu, 2005-03-24 at 20:48 -0500, Tom Lane wrote:
> In that case there's a datatype mismatch between the referencing and
> referenced columns, which prevents the index from being used for the
> FK check.
Can I have more words on this? Here is how I created the tables:
CREATE TABLE int_sensor_mea
On Thu, 2005-03-24 at 21:24 -0800, Josh Berkus wrote:
> Karim,
> How about getting some decent disk support? A single 10K SCSI disk is a bit
> sub-par for a database with 100's of millions of records. Too bad you didn't
> get a v40z ...
Hehe. I have one I am setting up that will be dedicated t
On Sat, 26 Mar 2005, Karim Nassar wrote:
> On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote:
> > On Sat, 26 Mar 2005, Karim Nassar wrote:
> >
> > > On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> > > > That seems like it should be okay, hmm, what does something like:
> > > >
> > >
On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote:
> On Sat, 26 Mar 2005, Karim Nassar wrote:
>
> > On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> > > That seems like it should be okay, hmm, what does something like:
> > >
> > > PREPARE test(int) AS SELECT 1 from measurement where
On Sat, 26 Mar 2005, Karim Nassar wrote:
> On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> > That seems like it should be okay, hmm, what does something like:
> >
> > PREPARE test(int) AS SELECT 1 from measurement where
> > id_int_sensor_meas_type = $1 FOR UPDATE;
> > EXPLAIN ANALYZE EXE
On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> That seems like it should be okay, hmm, what does something like:
>
> PREPARE test(int) AS SELECT 1 from measurement where
> id_int_sensor_meas_type = $1 FOR UPDATE;
> EXPLAIN ANALYZE EXECUTE TEST(1);
>
> give you as the plan?
On Fri, 25 Mar 2005, Karim Nassar wrote:
> On Fri, 2005-03-25 at 15:10 +, Simon Riggs wrote:
> > Karim: Did this happen? If not, can you drop and re-create and confirm
> > that you get the WARNING? If not, we have problems.
>
> No. Nor do I think that I should. SERIAL is shortcut for INTEGER,
There is clear benefit from forcing them to be the same. In logical data
terms, they *should* be the same. I don't check fruit.apple_grade
against fruit_type.orange_grade. When would I want to make a check of
that nature? If there is a reason, thats great, lets keep status quo
then.
I respect the e
On Fri, 2005-03-25 at 16:25 -0500, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Fri, 25 Mar 2005, Simon Riggs wrote:
> >> Could it be that because PostgreSQL has a very highly developed sense of
> >> datatype comparison that we might be taking this to extremes? Would any
> >>
On Fri, 2005-03-25 at 15:10 +, Simon Riggs wrote:
> Karim: Did this happen? If not, can you drop and re-create and confirm
> that you get the WARNING? If not, we have problems.
No. Nor do I think that I should. SERIAL is shortcut for INTEGER, no? I
think there is some other (TBD) problem causi
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 25 Mar 2005, Simon Riggs wrote:
>> Could it be that because PostgreSQL has a very highly developed sense of
>> datatype comparison that we might be taking this to extremes? Would any
>> other RDBMS consider two different datatypes to be comparable
On Fri, 25 Mar 2005, Simon Riggs wrote:
> On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > >>> Other than spec compliance, you mean? SQL99 says
> > >>>
> > >>> ... The declared type of each
On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> >>> Other than spec compliance, you mean? SQL99 says
> >>>
> >>> ... The declared type of each referencing column shall be
> >>> comparable to the
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
>>> Other than spec compliance, you mean? SQL99 says
>>>
>>> ... The declared type of each referencing column shall be
>>> comparable to the declared type of the corresponding referenced
>>> column.
> To
On Fri, 2005-03-25 at 08:23 -0800, Stephan Szabo wrote:
> On Fri, 25 Mar 2005, Simon Riggs wrote:
>
> > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > > > this situa
Karim,
> I guess I was wondering if there is other general tuning advice for such
> large table indexes such as increasing statistics, etc.
Well, your index use problem is being explained by Tom, Stephan and Simon;
basically your FKed data types are incompatible for index use purposes so the
sy
On Fri, 25 Mar 2005, Simon Riggs wrote:
> On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > > this situation to continue.
> >
> > Other than spec compliance, you mean? SQ
On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > this situation to continue.
>
> Other than spec compliance, you mean? SQL99 says
>
> ... The declared type of ea
Simon Riggs <[EMAIL PROTECTED]> writes:
> I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> this situation to continue.
Other than spec compliance, you mean? SQL99 says
... The declared type of each referencing column shall be
comparable to the decl
On Fri, 2005-03-25 at 01:58 -0500, Tom Lane wrote:
> Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> >> In that case there's a datatype mismatch between the referencing and
> >> referenced columns, which prevents the index from being used for the
> >> FK check.
>
> > Is creating such a forei
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> In that case there's a datatype mismatch between the referencing and
>> referenced columns, which prevents the index from being used for the
>> FK check.
> Is creating such a foreign key a WARNING yet?
I believe so as of 8.0. It's a bit tric
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> Hmm ... I wonder how hard it would be to teach EXPLAIN ANALYZE to show
>> the runtime expended in each trigger when the statement is of a kind
>> that has triggers.
> Could SPI "know" that an explain analyze is being run and add their
> outpu
On Thu, 24 Mar 2005, Tom Lane wrote:
Mark Lewis <[EMAIL PROTECTED]> writes:
I've got a similar problem with deletes taking a very long time. I know
that there are lots of foreign keys referencing this table, and other
foreign keys referencing those tables, etc. I've been curious, is there
a way t
Karim,
> Problem now is: this referencing table I expect to grow to about 110
> million rows in the next 2 months, then by 4 million rows per month
> thereafter. I expect that the time for recreating the foreign key will
> grow linearly with size.
>
> Is this just the kind of thing I need to watch
On Mar 24, 2005, at 10:38 PM, Christopher Kings-Lynne wrote:
In that case there's a datatype mismatch between the referencing and
referenced columns, which prevents the index from being used for the
FK check.
Is creating such a foreign key a WARNING yet?
I recall getting such a warning when importi
Watch your pg_stats_* views before and after the delete and check what
related tables have had lots of seqscans.
Chris
Mark Lewis wrote:
Tom,
I've got a similar problem with deletes taking a very long time. I know
that there are lots of foreign keys referencing this table, and other
foreign keys
In that case there's a datatype mismatch between the referencing and
referenced columns, which prevents the index from being used for the
FK check.
Is creating such a foreign key a WARNING yet?
Chris
---(end of broadcast)---
TIP 6: Have you searched o
Hmm ... I wonder how hard it would be to teach EXPLAIN ANALYZE to show
the runtime expended in each trigger when the statement is of a kind
that has triggers. We couldn't break down the time *within* the
triggers, but even this info would help a lot in terms of finger
pointing ...
Seq Scan
Mark Lewis <[EMAIL PROTECTED]> writes:
> I've got a similar problem with deletes taking a very long time. I know
> that there are lots of foreign keys referencing this table, and other
> foreign keys referencing those tables, etc. I've been curious, is there
> a way to find out how long the forei
Karim Nassar <[EMAIL PROTECTED]> writes:
>> Look at what your triggers are doing. My private bet is that you have
>> unindexed foreign keys referencing this table, and so each deletion
>> forces a seqscan of some other, evidently very large, table(s).
> Almost. I have a large table (6.3 million r
On Thu, 2005-03-24 at 19:52 -0500, Tom Lane wrote:
> Karim Nassar <[EMAIL PROTECTED]> writes:
> > Here is the statement:
>
> > orfs=# explain analyze DELETE FROM int_sensor_meas_type WHERE
> > id_meas_type IN (SELECT * FROM meas_type_ids);
> >
Tom,
I've got a similar problem with deletes taking a very long time. I know
that there are lots of foreign keys referencing this table, and other
foreign keys referencing those tables, etc. I've been curious, is there
a way to find out how long the foreign key checks take for each
dependent tab
Karim Nassar <[EMAIL PROTECTED]> writes:
> Here is the statement:
> orfs=# explain analyze DELETE FROM int_sensor_meas_type WHERE
> id_meas_type IN (SELECT * FROM meas_type_ids);
> QUERY PLAN
> --
59 matches
Mail list logo
