On Tuesday 06 January 2004 01:22, Rod Taylor wrote:
> Anyway, with Rules you can force this:
>
> ON INSERT UPDATE counter SET tablecount = tablecount + 1;
>
> ON DELETE UPDATE counter SET tablecount = tablecount - 1;
That would generate lot of dead tuples in counter table. How about
select relpag
On Tuesday 06 January 2004 07:16, Christopher Browne wrote:
> Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Paul
Tuckfield) wrote:
> > Not that I'm offering to do the porgramming mind you, :) but . .
> >
> > In the case of select count(*), one optimization is to do a scan of the
>
David Teran wrote:
> Index?Scan?using?key_value_meta_data__id_value__fk_index,?key_value_meta
> _data__id_value__fk_index?on?"KEY_VALUE_META_DATA"?t0??(cost=0.00..19.94
> ?rows=14?width=75)?(actual?time=0.112..0.296?rows=25?loops=1)
> ??Index?Cond:?(("ID_VALUE"?=?21094)?OR?("ID_VALUE"?=?21103))
>
[EMAIL PROTECTED] (Rod Taylor) wrote:
>> Especially with very large tables, hearing the disks grind as Postgres scans
>> every single row in order to determine the number of rows in a table or the
>> max value of a column (even a primary key created from a sequence) is pretty
>> painful. If the im
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Paul Tuckfield) wrote:
> Not that I'm offering to do the porgramming mind you, :) but . .
>
> In the case of select count(*), one optimization is to do a scan of the
> primary key, not the table itself, if the table has a primary key.
Paul Tuckfield <[EMAIL PROTECTED]> writes:
> In the case of select count(*), one optimization is to do a scan of the
> primary key, not the table itself, if the table has a primary key. In a
> certain commercial, lesser database, this is called an "index fast full
> scan". It would be important
Not that I'm offering to do the porgramming mind you, :) but . .
In the case of select count(*), one optimization is to do a scan of the
primary key, not the table itself, if the table has a primary key. In a
certain commercial, lesser database, this is called an "index fast full
scan". It wou
On Mon, 5 Jan 2004, Rod Taylor wrote:
> On Mon, 2004-01-05 at 14:48, Stephan Szabo wrote:
> > On Mon, 5 Jan 2004, Vivek Khera wrote:
> >
> > >
> > > On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote:
> > >
> > > > But, if he's updating the fk table but not the keyed column, it should
> > > > no
> >
Oops! [EMAIL PROTECTED] (John Siracusa) was seen spray-painting on a wall:
> Speaking of special cases (well, I was on the admin list) there are two
> kinds that would really benefit from some attention.
>
> 1. The query "select max(foo) from bar" where the column foo has an
> index. Aren't indexe
In the last exciting episode, [EMAIL PROTECTED] (John Siracusa) wrote:
> What column(s) should I increase? Do I have to do anything after increasing
> the statistics, or do I just wait for the stats collector to do its thing?
You have to ANALYZE the table again, to force in new statistics.
And i
John Siracusa <[EMAIL PROTECTED]> writes:
> 1. The query "select max(foo) from bar" where the column foo has an index.
> Aren't indexes ordered? If not, an "ordered index" would be useful in this
> situation so that this query, rather than doing a sequential scan of the
> whole table, would just "
On Mon, 2004-01-05 at 14:48, Stephan Szabo wrote:
> On Mon, 5 Jan 2004, Vivek Khera wrote:
>
> >
> > On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote:
> >
> > > But, if he's updating the fk table but not the keyed column, it should
> > > no
> > > longer be doing the check and grabbing the locks. I
On 1/5/04 2:52 PM, Rod Taylor wrote:
> max(foo) optimizations requires an extension to the aggregates system.
> It will likely happen within a few releases.
Looking forward to it.
> A work around can be accomplished today through the use of LIMIT and ORDER BY.
Wowzers, I never imagined that that
> Especially with very large tables, hearing the disks grind as Postgres scans
> every single row in order to determine the number of rows in a table or the
> max value of a column (even a primary key created from a sequence) is pretty
> painful. If the implementation is not too horrendous, this i
On Mon, 5 Jan 2004, Vivek Khera wrote:
>
> On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote:
>
> > But, if he's updating the fk table but not the keyed column, it should
> > no
> > longer be doing the check and grabbing the locks. If he's seeing it
> > grab
> > the row locks still a full test case
Hi Tom,
first of all thanks for your help! I really appreciate your fast
response and if you ever have a question about WebObjects, just drop me
line ;-)
Seq Scan on "KEY_VALUE_META_DATA" t0 (cost=0.00..2671.16 rows=931
width
=1068) (actual time=122.669..172.179 rows=25 loops=1)
Filter:
David Teran <[EMAIL PROTECTED]> writes:
> explain result from second query:
> Seq Scan on "KEY_VALUE_META_DATA" t0 (cost=0.00..2671.16 rows=931 width
> =1068) (actual time=122.669..172.179 rows=25 loops=1)
> Filter: (("ID_VALUE" = 21094) OR ("ID_VALUE" = 21103))
The problem is evidently that
Speaking of special cases (well, I was on the admin list) there are two
kinds that would really benefit from some attention.
1. The query "select max(foo) from bar" where the column foo has an index.
Aren't indexes ordered? If not, an "ordered index" would be useful in this
situation so that this
Hi Tom,
David Teran <[EMAIL PROTECTED]> writes:
What we found out now is that a query with a single 'where' works
fine,
the query planer uses the index but when we have 'two' where clauses
it
does not use the index anymore:
EXPLAIN ANALYZE SELECT columns... FROM "KEY_VALUE_META_DATA" t0 WHE
On Jan 5, 2004, at 1:57 PM, Stephan Szabo wrote:
But, if he's updating the fk table but not the keyed column, it should
no
longer be doing the check and grabbing the locks. If he's seeing it
grab
the row locks still a full test case would be handy because it'd
probably
mean we missed something
On Mon, 5 Jan 2004, Bruno Wolff III wrote:
> On Mon, Jan 05, 2004 at 11:33:40 -0500,
> Vivek Khera <[EMAIL PROTECTED]> wrote:
> >
> > Thanks. Then it sorta makes it moot for me to try deferred checks,
> > since the Pimary and Foreign keys never change once set. I wonder
> > what is making the
On Jan 5, 2004, at 1:38 PM, Bruno Wolff III wrote:
I think this is probably the issue with foreign key checks needing an
exclusive lock, since there is no shared lock that will prevent
deletes.
That was my original thought upon reading all the discussion of late
regarding the FK checking locks.
David Teran <[EMAIL PROTECTED]> writes:
> What we found out now is that a query with a single 'where' works fine,
> the query planer uses the index but when we have 'two' where clauses it
> does not use the index anymore:
> EXPLAIN ANALYZE SELECT columns... FROM "KEY_VALUE_META_DATA" t0 WHERE
Hi Tom,
It's worth pointing out that this problem is fixed (at long last) in
CVS tip. Ypu probably shouldn't expend large amounts of effort on
working around a problem that will go away in 7.5.
We have now changed the definition to integer, this will work for some
time. We are currently evaluati
On Mon, Jan 05, 2004 at 11:33:40 -0500,
Vivek Khera <[EMAIL PROTECTED]> wrote:
>
> Thanks. Then it sorta makes it moot for me to try deferred checks,
> since the Pimary and Foreign keys never change once set. I wonder
> what is making the transactions appear to run lockstep, then...
I think t
On 1/5/04 11:45 AM, Christopher Browne wrote:
> It sounds to me as though the statistics that are being collected
> aren't "good enough." That tends to be a sign that the quantity of
> statistics (e.g. - bins in the histogram) are insufficient.
>
> This would be resolved by changing the number of
After a long battle with technology, [EMAIL PROTECTED] (John Siracusa), an earthling,
wrote:
> On 1/5/04 1:55 AM, Tom Lane wrote:
>> John Siracusa <[EMAIL PROTECTED]> writes:
>>> Obviously the planner is making some bad choices here.
>>
>> A fair conclusion ...
>>
>>> I know that it is trying to
> "CK" == Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
>> One more question: does the FK checker know to skip checking a
>> constraint if the column in question did not change during an update?
CK> As of 7.4, yes the check is skipped.
Thanks. Then it sorta makes it moot for me to tr
John Siracusa <[EMAIL PROTECTED]> writes:
> Is there any way to "uncluster" a table? Should I just cluster it on a
> different column?
That should work, if you choose one that's uncorrelated with the
previous clustering attribute.
regards, tom lane
--
On 1/5/04 1:55 AM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> writes:
>> Obviously the planner is making some bad choices here.
>
> A fair conclusion ...
>
>> I know that it is trying to avoid random seeks or other scary things
>> implied by a "correlation" statistic that is not close to
David Teran <[EMAIL PROTECTED]> writes:
> This is my bigger problem: i am using EOF (OR mapping tool) which frees
> me more or less form writing a lot of SQL. If i need to typecast to use
> an index then i have to see how to do this with this framework.
It's worth pointing out that this problem
explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE
t0.ID_FOREIGN_TABLE = 21110::bigint;
an index is used. Very fine, the performance is about 10 to 100 times
faster for the single select.
An alternative technique is to do this:
... t0.ID_FOREIGN_TABLE = '21110';
Chris
-
On Monday 05 January 2004 17:48, David Teran wrote:
> Hi,
>
> > The performance will likely to be the same. Its just that integer
> > happens to
> > be default integer type and hence it does not need an explicit
> > typecast. ( I
> > don't remember exactly which integer is default but it is either
Hi,
The performance will likely to be the same. Its just that integer
happens to
be default integer type and hence it does not need an explicit
typecast. ( I
don't remember exactly which integer is default but it is either of
int2,int4
and int8...:-))
The docs say int4 is much faster than int8
On Monday 05 January 2004 17:35, David Teran wrote:
> explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE
> t0.ID_FOREIGN_TABLE = 21110;
>
> i see that no index is being used whereas when i use
>
> explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE
> t0.ID_FOREIGN
Hi Shridhar,
Are you sure you are using correct data types on indexes?
Did not know about this...
e.g. if field1 is an int2 field, then following query would not use an
index.
our fk have the type bigint, when i try one simple select like this:
explain analyze SELECT --columns-- FROM KEY_VALU
On Monday 05 January 2004 16:58, David Teran wrote:
> We have some tests to check the performance and FrontBase is about 10
> times faster than Postgres. We already played around with explain
> analyse select. It seems that for large tables Postgres does not use an
> index. We often see the scan me
Hi,
we are new to Postgres and we are evaluating Postgres 7.4 on MacOS X as
an alternative to FrontBase 3.6.27.
From the available features Postgres is the choice #1.
We have some tests to check the performance and FrontBase is about 10
times faster than Postgres. We already played around with
John Siracusa <[EMAIL PROTECTED]> writes:
> Obviously the planner is making some bad choices here.
A fair conclusion ...
> I know that it is trying to avoid random seeks or other scary things
> implied by a "correlation" statistic that is not close to 1 or -1, but
> it is massively overestimating
39 matches
Mail list logo
