pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Anomalies in 7.4.5
15+ messages / 10 participants
[nested] [flat]
* Performance Anomalies in 7.4.5
@ 2004-10-21 20:36 Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 5 replies; 15+ messages in thread
From: Thomas F.O'Connell @ 2004-10-21 20:36 UTC (permalink / raw)
To: pgsql-performance
I'm seeing some weird behavior on a repurposed server that was wiped
clean and set up to run as a database and application server with
postgres and Apache, as well as some command-line PHP scripts.
The box itself is a quad processor (2.4 GHz Intel Xeons) Debian woody
GNU/Linux (2.6.2) system.
postgres is crawling on some fairly routine queries. I'm wondering if
this could somehow be related to the fact that this isn't a
database-only server, but Apache is not really using any resources when
postgres slows to a crawl.
Here's an example of analysis of a recent query:
EXPLAIN ANALYZE SELECT COUNT(DISTINCT u.id)
FROM userdata as u, userdata_history as h
WHERE h.id = '18181'
AND h.id = u.id;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
Aggregate (cost=0.02..0.02 rows=1 width=8) (actual
time=298321.421..298321.422 rows=1 loops=1)
-> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
time=1.771..298305.531 rows=2452 loops=1)
Join Filter: ("inner".id = "outer".id)
-> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
(actual time=0.026..11.869 rows=2452 loops=1)
-> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
Filter: (id = 18181::bigint)
Total runtime: 298321.926 ms
(7 rows)
userdata has a primary/foreign key on id, which references
userdata_history.id, which is a primary key.
At the time of analysis, the userdata table had < 2,500 rows.
userdata_history had < 50,000 rows. I can't imagine how even a seq scan
could result in a runtime of nearly 5 minutes in these circumstances.
Also, doing a count( * ) from each table individually returns nearly
instantly.
I can provide details of postgresql.conf and kernel settings if
necessary, but I'm using some pretty well tested settings that I use
any time I admin a postgres installation these days based on box
resources and database size. I'm more interested in knowing if there
are any bird's eye details I should be checking immediately.
Thanks.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 20:50 Thomas F.O'Connell <tfo@sitening.com>
parent: Thomas F.O'Connell <tfo@sitening.com>
4 siblings, 0 replies; 15+ messages in thread
From: Thomas F.O'Connell @ 2004-10-21 20:50 UTC (permalink / raw)
To: pgsql-performance
I know, I know: I should've done this before I posted. REINDEXing and
VACUUMing mostly fixed this problem. Which gets me back to where I was
yesterday, reviewing an import process (that existed previously) that
populates tables in this system that seems to allow small data sets to
cause simple queries like this to crawl. Is there anything about
general COPY/INSERT activity that can cause small data sets to become
so severely slow in postgres that can be prevented other than being
diligent about VACUUMing? I was hoping that pg_autovacuum along with
post-import manual VACUUMs would be sufficient, but it doesn't seem to
be the case necessarily. Granted, I haven't done a methodical and
complete review of the process, but I'm still surprised at how quickly
it seems able to debilitate postgres with even small amounts of data. I
had a similar situation crawl yesterday based on a series of COPYs
involving 5 rows!
As in, can I look for something to treat the cause rather than the
symptoms?
If not, should I be REINDEXing manually, as well as VACUUMing manually
after large data imports (whether via COPY or INSERT)? Or will a VACUUM
FULL ANALYZE be enough?
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 21, 2004, at 3:36 PM, Thomas F.O'Connell wrote:
> I'm seeing some weird behavior on a repurposed server that was wiped
> clean and set up to run as a database and application server with
> postgres and Apache, as well as some command-line PHP scripts.
>
> The box itself is a quad processor (2.4 GHz Intel Xeons) Debian woody
> GNU/Linux (2.6.2) system.
>
> postgres is crawling on some fairly routine queries. I'm wondering if
> this could somehow be related to the fact that this isn't a
> database-only server, but Apache is not really using any resources
> when postgres slows to a crawl.
>
> Here's an example of analysis of a recent query:
>
> EXPLAIN ANALYZE SELECT COUNT(DISTINCT u.id)
> FROM userdata as u, userdata_history as h
> WHERE h.id = '18181'
> AND h.id = u.id;
>
> QUERY PLAN
> -----------------------------------------------------------------------
> -----------------------------------------------------------------------
> --
> Aggregate (cost=0.02..0.02 rows=1 width=8) (actual
> time=298321.421..298321.422 rows=1 loops=1)
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
> Total runtime: 298321.926 ms
> (7 rows)
>
> userdata has a primary/foreign key on id, which references
> userdata_history.id, which is a primary key.
>
> At the time of analysis, the userdata table had < 2,500 rows.
> userdata_history had < 50,000 rows. I can't imagine how even a seq
> scan could result in a runtime of nearly 5 minutes in these
> circumstances.
>
> Also, doing a count( * ) from each table individually returns nearly
> instantly.
>
> I can provide details of postgresql.conf and kernel settings if
> necessary, but I'm using some pretty well tested settings that I use
> any time I admin a postgres installation these days based on box
> resources and database size. I'm more interested in knowing if there
> are any bird's eye details I should be checking immediately.
>
> Thanks.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 21:04 Bricklen <bricklen@zyahoo.zcomz>
parent: Thomas F.O'Connell <tfo@sitening.com>
4 siblings, 0 replies; 15+ messages in thread
From: Bricklen @ 2004-10-21 21:04 UTC (permalink / raw)
To: pgsql-performance
Thomas F.O'Connell wrote:
> I'm seeing some weird behavior on a repurposed server that was wiped
> clean and set up to run as a database and application server with
> postgres and Apache, as well as some command-line PHP scripts.
>
> The box itself is a quad processor (2.4 GHz Intel Xeons) Debian woody
> GNU/Linux (2.6.2) system.
>
> postgres is crawling on some fairly routine queries. I'm wondering if
> this could somehow be related to the fact that this isn't a
> database-only server, but Apache is not really using any resources when
> postgres slows to a crawl.
>
> Here's an example of analysis of a recent query:
>
> EXPLAIN ANALYZE SELECT COUNT(DISTINCT u.id)
> FROM userdata as u, userdata_history as h
> WHERE h.id = '18181'
> AND h.id = u.id;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> Aggregate (cost=0.02..0.02 rows=1 width=8) (actual
> time=298321.421..298321.422 rows=1 loops=1)
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
> Total runtime: 298321.926 ms
> (7 rows)
>
> userdata has a primary/foreign key on id, which references
> userdata_history.id, which is a primary key.
>
> At the time of analysis, the userdata table had < 2,500 rows.
> userdata_history had < 50,000 rows. I can't imagine how even a seq scan
> could result in a runtime of nearly 5 minutes in these circumstances.
>
> Also, doing a count( * ) from each table individually returns nearly
> instantly.
>
> I can provide details of postgresql.conf and kernel settings if
> necessary, but I'm using some pretty well tested settings that I use
> any time I admin a postgres installation these days based on box
> resources and database size. I'm more interested in knowing if there
> are any bird's eye details I should be checking immediately.
>
> Thanks.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
Is your enable_seqscan set to true?
Try it after issuing set enable_seqscan to off;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 21:05 Dennis Bjorklund <db@zigo.dhs.org>
parent: Thomas F.O'Connell <tfo@sitening.com>
4 siblings, 1 reply; 15+ messages in thread
From: Dennis Bjorklund @ 2004-10-21 21:05 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
On Thu, 21 Oct 2004, Thomas F.O'Connell wrote:
> Aggregate (cost=0.02..0.02 rows=1 width=8) (actual
> time=298321.421..298321.422 rows=1 loops=1)
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
It looks like you have not run ANALYZE recently. Most people run VACUUM
ANALYZE every night (or similar) in a cron job.
--
/Dennis Björklund
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 21:11 Thomas F.O'Connell <tfo@sitening.com>
parent: Dennis Bjorklund <db@zigo.dhs.org>
0 siblings, 0 replies; 15+ messages in thread
From: Thomas F.O'Connell @ 2004-10-21 21:11 UTC (permalink / raw)
To: Dennis Bjorklund <db@zigo.dhs.org>; +Cc: pgsql-performance
The irony is that I had just disabled pg_autovacuum the previous day
during analysis of a wider issue affecting imports of data into the
system.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 21, 2004, at 4:05 PM, Dennis Bjorklund wrote:
> On Thu, 21 Oct 2004, Thomas F.O'Connell wrote:
>
>> Aggregate (cost=0.02..0.02 rows=1 width=8) (actual
>> time=298321.421..298321.422 rows=1 loops=1)
>> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
>> time=1.771..298305.531 rows=2452 loops=1)
>> Join Filter: ("inner".id = "outer".id)
>> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
>> (actual time=0.026..11.869 rows=2452 loops=1)
>> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
>> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
>> Filter: (id = 18181::bigint)
>
> It looks like you have not run ANALYZE recently. Most people run VACUUM
> ANALYZE every night (or similar) in a cron job.
>
> --
> /Dennis Björklund
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 21:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas F.O'Connell <tfo@sitening.com>
4 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2004-10-21 21:53 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
> Total runtime: 298321.926 ms
> (7 rows)
What's killing you here is that the planner thinks these tables are
completely empty (notice the zero cost estimates, which implies the
table has zero blocks --- the fact that the rows estimate is 1 and not 0
is the result of sanity-check clamping inside costsize.c). This leads
it to choose a nestloop, which would be the best plan if there were only
a few rows involved, but it degenerates rapidly when there are not.
It's easy to fall into this trap when truncating and reloading tables;
all you need is an "analyze" while the table is empty. The rule of
thumb is to analyze just after you reload the table, not just before.
I'm getting more and more convinced that we need to drop the reltuples
and relpages entries in pg_class, in favor of checking the physical
table size whenever we make a plan. We could derive the tuple count
estimate by having ANALYZE store a tuples-per-page estimate in pg_class
and then multiply by the current table size; tuples-per-page should be
a much more stable figure than total tuple count.
One drawback to this is that it would require an additional lseek per
table while planning, but that doesn't seem like a huge penalty.
Probably the most severe objection to doing things this way is that the
selected plan could change unexpectedly as a result of the physical
table size changing. Right now the DBA can keep tight rein on actions
that might affect plan selection (ie, VACUUM and ANALYZE), but that
would go by the board with this. OTOH, we seem to be moving towards
autovacuum, which also takes away any guarantees in this department.
In any case this is speculation for 8.1; I think it's too late for 8.0.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-21 22:21 Gary Doades <gpd@gpdnet.co.uk>
parent: Thomas F.O'Connell <tfo@sitening.com>
4 siblings, 0 replies; 15+ messages in thread
From: Gary Doades @ 2004-10-21 22:21 UTC (permalink / raw)
To: pgsql-performance
On 21 Oct 2004 at 15:50, Thomas F.O'Connell wrote:
> If not, should I be REINDEXing manually, as well as VACUUMing manually
> after large data imports (whether via COPY or INSERT)? Or will a VACUUM
> FULL ANALYZE be enough?
>
It's not the vacuuming that's important here, just the analyze. If you import any data into
a table, Postgres often does not *know* that until you gather the statistics on the table.
You are simply running into the problem of the planner not knowing how much
data/distribution of data in your tables.
If you have large imports it may be faster overall to drop the indexes first, then insert the
data, then put the indexes back on, then analyze.
Cheers,
Gary.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-22 19:40 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Andrew Sullivan @ 2004-10-22 19:40 UTC (permalink / raw)
To: pgsql-performance
> Probably the most severe objection to doing things this way is that the
> selected plan could change unexpectedly as a result of the physical
> table size changing. Right now the DBA can keep tight rein on actions
> that might affect plan selection (ie, VACUUM and ANALYZE), but that
> would go by the board with this. OTOH, we seem to be moving towards
> autovacuum, which also takes away any guarantees in this department.
But aren't we requiring that we can disable autovacuum on some
tables? I've actually used, more than once, the finger-on-the-scale
method of thumping values in pg_class when I had a pretty good idea
of how the table was changing, particularly when it would change in
such a way as to confuse the planner. There are still enough cases
where the planner doesn't quite get things right that I'd really
prefer the ability to give it clues, at least indirectly. I can't
imagine that there's going to be a lot of enthusiasm for hints, so
anything that isn't a sure-fire planner helper is a potential loss,
at least to me.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-22 21:13 Matthew T. O'Connor <matthew@zeut.net>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 15+ messages in thread
From: Matthew T. O'Connor @ 2004-10-22 21:13 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance
Andrew Sullivan wrote:
>>Probably the most severe objection to doing things this way is that the
>>selected plan could change unexpectedly as a result of the physical
>>table size changing. Right now the DBA can keep tight rein on actions
>>that might affect plan selection (ie, VACUUM and ANALYZE), but that
>>would go by the board with this. OTOH, we seem to be moving towards
>>autovacuum, which also takes away any guarantees in this department.
>>
>>
>
>But aren't we requiring that we can disable autovacuum on some
>tables?
>
Yes that is the long term goal, but the autovac in 8.0 is still all or
nothing.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-25 13:51 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 0 replies; 15+ messages in thread
From: Andrew Sullivan @ 2004-10-25 13:51 UTC (permalink / raw)
To: pgsql-performance
On Fri, Oct 22, 2004 at 05:13:18PM -0400, Matthew T. O'Connor wrote:
> Yes that is the long term goal, but the autovac in 8.0 is still all or
> nothing.
Yes, which is why I couldn't use the current iteration for
production: the cost is too high. I think this re-inforces my
original point, which is that taking away the ability of DBAs to
thump the planner for certain tables -- even indirectly -- under
certain pathological conditions is crucial for production work. In
the ideal world, the wizards and genius planners and such like would
work perfectly, and the DBA would never have to intervene. In
practice, there are cases when you need to haul on a knob or two.
While this doesn't mean that we should adopt the Oracle approach of
having knobs which adjust the sensitivity of the knob that tunes the
main knob-tuner, I'm still pretty leery of anything which smacks of
completely locking the DBA's knowledge out of the system.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-28 08:01 Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Alban Medici (NetCentrex) @ 2004-10-28 08:01 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Thomas F.O'Connell' <tfo@sitening.com>; +Cc: pgsql-performance
This topic probably available in 8.x will be very usefull for people just
using postgresql as a "normal" Database user.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
Sent: jeudi 21 octobre 2004 23:53
To: Thomas F.O'Connell
Cc: PgSQL - Performance
Subject: Re: [PERFORM] Performance Anomalies in 7.4.5
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> -> Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual
> time=1.771..298305.531 rows=2452 loops=1)
> Join Filter: ("inner".id = "outer".id)
> -> Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.026..11.869 rows=2452 loops=1)
> -> Seq Scan on userdata_history h (cost=0.00..0.00 rows=1
> width=8) (actual time=0.005..70.519 rows=41631 loops=2452)
> Filter: (id = 18181::bigint)
> Total runtime: 298321.926 ms
> (7 rows)
What's killing you here is that the planner thinks these tables are
completely empty (notice the zero cost estimates, which implies the table
has zero blocks --- the fact that the rows estimate is 1 and not 0 is the
result of sanity-check clamping inside costsize.c). This leads it to choose
a nestloop, which would be the best plan if there were only a few rows
involved, but it degenerates rapidly when there are not.
It's easy to fall into this trap when truncating and reloading tables; all
you need is an "analyze" while the table is empty. The rule of thumb is to
analyze just after you reload the table, not just before.
I'm getting more and more convinced that we need to drop the reltuples and
relpages entries in pg_class, in favor of checking the physical table size
whenever we make a plan. We could derive the tuple count estimate by having
ANALYZE store a tuples-per-page estimate in pg_class and then multiply by
the current table size; tuples-per-page should be a much more stable figure
than total tuple count.
One drawback to this is that it would require an additional lseek per table
while planning, but that doesn't seem like a huge penalty.
Probably the most severe objection to doing things this way is that the
selected plan could change unexpectedly as a result of the physical table
size changing. Right now the DBA can keep tight rein on actions that might
affect plan selection (ie, VACUUM and ANALYZE), but that would go by the
board with this. OTOH, we seem to be moving towards autovacuum, which also
takes away any guarantees in this department.
In any case this is speculation for 8.1; I think it's too late for 8.0.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-28 16:07 Josh Berkus <josh@agliodbs.com>
parent: Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
0 siblings, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2004-10-28 16:07 UTC (permalink / raw)
To: pgsql-performance
Tom,
> One drawback to this is that it would require an additional lseek per table
> while planning, but that doesn't seem like a huge penalty.
Hmmm ... would the additional lseek take longer for larger tables, or would it
be a fixed cost?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-28 16:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2004-10-28 16:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
>> One drawback to this is that it would require an additional lseek per table
>> while planning, but that doesn't seem like a huge penalty.
> Hmmm ... would the additional lseek take longer for larger tables, or would it
> be a fixed cost?
Should be pretty much a fixed cost: one kernel call per table.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-28 17:20 Rod Taylor <pg@rbt.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Rod Taylor @ 2004-10-28 17:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Thu, 2004-10-28 at 12:31, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> >> One drawback to this is that it would require an additional lseek per table
> >> while planning, but that doesn't seem like a huge penalty.
>
> > Hmmm ... would the additional lseek take longer for larger tables, or would it
> > be a fixed cost?
>
> Should be pretty much a fixed cost: one kernel call per table.
Is this something that the bgwriter could periodically do and share the
data? Possibly in the future it could even force a function or prepared
statement recompile if the data has changed significantly?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Anomalies in 7.4.5
@ 2004-10-28 17:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2004-10-28 17:50 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Rod Taylor <pg@rbt.ca> writes:
> On Thu, 2004-10-28 at 12:31, Tom Lane wrote:
>> Should be pretty much a fixed cost: one kernel call per table.
> Is this something that the bgwriter could periodically do and share the
> data?
I think a kernel call would be cheaper.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2004-10-28 17:50 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-21 20:36 Performance Anomalies in 7.4.5 Thomas F.O'Connell <tfo@sitening.com>
2004-10-21 20:50 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-21 21:04 ` Bricklen <bricklen@zyahoo.zcomz>
2004-10-21 21:05 ` Dennis Bjorklund <db@zigo.dhs.org>
2004-10-21 21:11 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-21 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-22 19:40 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-10-22 21:13 ` Matthew T. O'Connor <matthew@zeut.net>
2004-10-25 13:51 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-10-28 08:01 ` Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
2004-10-28 16:07 ` Josh Berkus <josh@agliodbs.com>
2004-10-28 16:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-28 17:20 ` Rod Taylor <pg@rbt.ca>
2004-10-28 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-21 22:21 ` Gary Doades <gpd@gpdnet.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox