Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries in functions
Tom Lane wrote: John Meinel <[EMAIL PROTECTED]> writes: ... However, if I try to bundle this query up into a server side function, it runs very slow (10 seconds). I'm trying to figure out why, but since I can't run EXPLAIN ANALYZE inside a function, I don't really know what else to do. A parameterized query inside a function is basically the same as a PREPARE'd query with parameters at the SQL level. So you can investigate what's happening here with PREPARE foo(int) AS SELECT * FROM object WHERE id in ( SELECT id FROM data_t WHERE project_id = $1 UNION SELECT ... ; EXPLAIN ANALYZE EXECUTE foo(48542); I'm not sure where the problem is either, so please do send along the results. regards, tom lane PS: pgsql-performance would be a more appropriate venue for this discussion. Well, I think I tracked the problem down to the fact that the column does not have a "not null" constraint on it. Here is a demonstration. Basically, I have 3 tables, tobjects, tdata, and tproject. tdata basically just links between tobjects and tproject, but isn't required to link to tproject. Yes, the real data has more columns, but this shows the problem. jfmeinel=> \d tobjects Table "public.tobjects" Column | Type | Modifiers +-+--- id | integer | not null Indexes: "tobjects_pkey" primary key, btree (id) jfmeinel=> \d tproject Table "public.tproject" Column | Type | Modifiers +-+--- id | integer | not null Indexes: "tproject_pkey" primary key, btree (id) jfmeinel=> \d tdata Table "public.tdata" Column | Type | Modifiers +-+--- id | integer | not null project_id | integer | Indexes: "tdata_pkey" primary key, btree (id) "tdata_project_id_idx" btree (project_id) Foreign-key constraints: "tdata_id_fkey" FOREIGN KEY (id) REFERENCES tobjects(id) ON UPDATE CASCADE ON DELETE CASCADE "tdata_project_id_fkey" FOREIGN KEY (project_id) REFERENCES tproject(id) ON UPDATE CASCADE ON DELETE SET DEFAULT jfmeinel=> select count(*) from tdata; count 545768 jfmeinel=> select count(*) - count(project_id) from tdata; ?column? -- 240 So tdata(project_id) is almost completely full, of the 54+ entries, only 240 are null. jfmeinel=> prepare myget(int) as select id from tdata jfmeinel-> where project_id = $1; PREPARE jfmeinel=> explain analyze execute myget(3); QUERY PLAN Seq Scan on tdata (cost=0.00..9773.10 rows=181923 width=4) (actual time=1047.000..1047.000 rows=0 loops=1) Filter: (project_id = $1) Total runtime: 1047.000 ms jfmeinel=> explain analyze select id from tdata where project_id = 3; QUERY PLAN - Index Scan using tdata_project_id_idx on tdata (cost=0.00..4.20 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops =1) Index Cond: (project_id = 3) Total runtime: 0.000 ms So notice that when doing the actual select it is able to do the index query. But for some reason with a prepared statement, it is not able to do it. Any ideas? Since I only have the integers now, I can send the data to someone if they care to investigate it. It comes to 2.2M as a .tar.bz2, so obviously I'm not going to spam the list. If I rewrite myget as: prepare myget(int) as select id from tdata where project_id = 3; it does the right thing again. So it's something about how a variable interacts with an indexed column with null values. Note: I've tried creating a script that generates dummy data to show this problem and I have failed (it always performed the query correctly.) But this test data definitely shows the problem. And yes, I've vacuum analyzed all over the place. John =:-> PS> I tested this on PostgreSQL 7.4.3, and it did not demonstrate this problem. I am using PostgreSQL 8.0.0beta2 (probably -dev1) signature.asc Description: OpenPGP digital signature
Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries
John Meinel wrote: So notice that when doing the actual select it is able to do the index query. But for some reason with a prepared statement, it is not able to do it. Any ideas? In the index-using example, PG knows the value you are comparing to. So, it can make a better estimate of how many rows will be returned. With the prepared/compiled version it has to come up with a plan that makes sense for any value. If you look back at the explain output you'll see PG is guessing 181,923 rows will match with the prepared query but only 1 for the second query. If in fact you returned that many rows, you wouldn't want to use the index - it would mean fetching values twice. The only work-around if you are using plpgsql functions is to use EXECUTE to make sure your queries are planned for each value provided. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] This query is still running after 10 hours...
Hi Robert,
"There is no significant disk activity (read 0), one CPU is pegged, and
that process is consuming 218M Resident memory, 168M Shared (10% available
memory total). All reasonable, except for the fact it doesn't come back..."
Just to let you know, I've observed the identical phenomenon on my RHEL3-WS
server running PostgreSQL V7.3.4: One of the CPU's pegged at 100% (2-way
SMP with hyperthreading, so 4 apparent CPU's), virtually zero disk I/O
activity, high memory usage, etc. I thought it might be due to a casting
problem in a JOIN's ON clause, but that did not turn out to be the case. I
*have* recently observed that if I run a vacuum analyze on the entire
database, the amount of time spent in this looping state decreases greatly,
but it has *not* disappeared in all cases.
Next week I hope to be able to run some directed test with stats collection
turned on, to try to see if I can find out what's causing this to occur.
I'll post the results if I find anything significant.
--- Steve
___
Steven Rosenstein
Senior IT Architect/Specialist | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com
"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt
Robert Creager
<[EMAIL PROTECTED]
ogicalChaos.org> To
Sent by: Tom Lane <[EMAIL PROTECTED]>
pgsql-performance cc
[EMAIL PROTECTED] PGPerformance
.org <[EMAIL PROTECTED]>
Subject
Re: [PERFORM] This query is still
09/28/2004 11:44 running after 10 hours...
PM
When grilled further on (Tue, 28 Sep 2004 11:04:23 -0400),
Tom Lane <[EMAIL PROTECTED]> confessed:
> Robert Creager <[EMAIL PROTECTED]> writes:
> > Normally, this query takes from 5 minutes to 2 hours to run. On this
> > update, it's been running for more than 10 hours.
>
> > ...
> > -> Nested Loop (cost=250.69..129094.19 rows=77211 width=59)
> > -> Hash Join (cost=250.69..307.34 rows=67 width=12)
> > Hash Cond: ("outer".pair_id = "inner".pair_id)
> > ...
>
> It chose a nested loop here because it was only expecting 67 rows out of
> the next-lower join, and so it thought it would only need 67 repetitions
> of the index probe into obs_v_file_id_index. I'm suspicious that that
> estimate was way low and so the nestloop is taking forever. You might
> try "SET enable_nestloop = off" as a crude way of avoiding that trap.
I tried your suggestion. Did generate a different plan (below), but the
estimation is blown as it still used a nested loop. The query is currently
running(42 minutes so far). For the query in question, there are 151
different
pair_id's in the pairs table, which equates to 302 entries in the files
table
(part of the query), which moves on to 533592 entries in the obs_v table
and
533699 entries in the obs_i table.
The groups table has 76 total entries, files 9028, zero_pair 2532,
color_groups
147. Only the obs_v and obs_i tables have data of any significant
quantities
with 10M rows apiece. The trigger hitting the catalog table (875499
entries) is
searching for single entries to match (one fire per obs_v/obs_i update) on
an
index (took 54ms on the first query of a random id just now).
There is no significant disk activity (read 0), one CPU is pegged, and that
process is consuming 218M Resident memory, 168M Shared (10% available
memory
total). All reasonable, except for the fact it doesn't come back...
Hash Join (cost=100267870.17..100751247.13 rows=1578889 width=63)
Hash Cond: ("outer".star_id = "inner".star_id)
-> Seq Scan on obs_i i (cost=0.00..213658.19 rows=10391319 width=8)
-> Hash (cost=100266886.39..100266886.39 rows=77113 width=59)
-> Hash Join (cost=10307.51..100266886.39 rows=77113
width=59)
Hash Cond: ("outer".fil
Re: [PERFORM] Interest in perf testing?
What is involved, rather what kind of help do you require? Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus Sent: Tuesday, September 28, 2004 1:54 PM To: [EMAIL PROTECTED] Subject: [PERFORM] Interest in perf testing? Folks, I'm beginning a series of tests on OSDL's Scalable Test Platform in order to determine some recommended settings for many of the new PostgreSQL.conf parameters as well as pg_autovacuum. Is anyone else interested in helping me with this? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries
Richard Huxton wrote: John Meinel wrote: So notice that when doing the actual select it is able to do the index query. But for some reason with a prepared statement, it is not able to do it. Any ideas? In the index-using example, PG knows the value you are comparing to. So, it can make a better estimate of how many rows will be returned. With the prepared/compiled version it has to come up with a plan that makes sense for any value. If you look back at the explain output you'll see PG is guessing 181,923 rows will match with the prepared query but only 1 for the second query. If in fact you returned that many rows, you wouldn't want to use the index - it would mean fetching values twice. The only work-around if you are using plpgsql functions is to use EXECUTE to make sure your queries are planned for each value provided. I suppose that make sense. If the number was small (< 100) then there probably would be a lot of responses. Because the tproject table is all small integers. But for a large number, it probably doesn't exist on that table at all. Thanks for the heads up. John =:-> signature.asc Description: OpenPGP digital signature
[PERFORM] why does explain analyze differ so much from estimated explain?
Hi,
I have a query where I do not understand that the rows number that
explain analyze finds differs so much from what explain estimates (3rd
nested loop estimates 1 row but in real it is 4222 rows). I did analyze
the tables (pgsql 7.4.1).
Here is the query:
explain analyze
SELECT fts.val_1, max(fts.val_2) AS val_2
FROM docobjflat AS fts,
boxinfo,
docobjflat AS ftw0,
docobjflat AS ftw, envspec_map
WHERE boxinfo.member=158096693
AND boxinfo.envelope=ftw.envelope
AND boxinfo.community=169964332
AND boxinfo.hide=FALSE
AND ftw0.flatid=ftw.flatid
AND fts.flatid=ftw.flatid
AND fts.docstart=1
AND envspec_map.spec=169964482
AND envspec_map.community=boxinfo.community
AND envspec_map.envelope=boxinfo.envelope
AND ftw0.val_14='IN-A01'
GROUP BY fts.val_1;
Query plan is attached.
Regards Dirk
QUERY PLAN
-
HashAggregate (cost=134.58..134.58 rows=1 width=12) (actual time=1218.479..1218.480
rows=1 loops=1)
-> Nested Loop (cost=0.00..134.57 rows=1 width=12) (actual time=723.208..1218.167
rows=173 loops=1)
-> Nested Loop (cost=0.00..110.49 rows=1 width=42) (actual
time=0.687..636.649 rows=4222 loops=1)
-> Nested Loop (cost=0.00..86.39 rows=1 width=15) (actual
time=0.567..28.520 rows=4222 loops=1)
-> Nested Loop (cost=0.00..38.04 rows=1 width=8) (actual
time=0.394..6.078 rows=43 loops=1)
-> Index Scan using boxinfo_audi_index on boxinfo
(cost=0.00..16.89 rows=4 width=8) (actual time=0.190..2.791 rows=165 loops=1)
Index Cond: (member = 158096693::oid)
Filter: ((community = 169964332::oid) AND (hide =
false))
-> Index Scan using envspec_169964482_index on envspec_map
(cost=0.00..5.28 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=165)
Index Cond: ((envspec_map.envelope =
"outer".envelope) AND (envspec_map.community = 169964332::oid))
Filter: (spec = 169964482)
-> Index Scan using docobjflat_169964482_envelope on
docobjflat_169964482 ftw (cost=0.00..47.31 rows=83 width=19) (actual
time=0.049..0.291 rows=98 loops=43)
Index Cond: ("outer".envelope = ftw.envelope)
-> Index Scan using docobjflat_169964482_flatid on
docobjflat_169964482 fts (cost=0.00..24.07 rows=2 width=27) (actual time=0.010..0.138
rows=1 loops=4222)
Index Cond: (fts.flatid = "outer".flatid)
Filter: (docstart = 1)
-> Index Scan using docobjflat_169964482_flatid on docobjflat_169964482 ftw0
(cost=0.00..24.07 rows=1 width=15) (actual time=0.135..0.135 rows=0 loops=4222)
Index Cond: ("outer".flatid = ftw0.flatid)
Filter: (val_14 = 'IN-A01'::text)
Total runtime: 1219.200 ms
(20 rows)
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries in functions
[ enlarging on Richard's response a bit ] John Meinel <[EMAIL PROTECTED]> writes: > jfmeinel=> explain analyze execute myget(3); > QUERY PLAN > > Seq Scan on tdata (cost=0.00..9773.10 rows=181923 width=4) > (actual time=1047.000..1047.000 rows=0 loops=1) > Filter: (project_id = $1) > Total runtime: 1047.000 ms > jfmeinel=> explain analyze select id from tdata where project_id = 3; > QUERY PLAN > - > Index Scan using tdata_project_id_idx on tdata (cost=0.00..4.20 > rows=1 width=4) (actual time=0.000..0.000 rows=0 loops =1) > Index Cond: (project_id = 3) > Total runtime: 0.000 ms > So notice that when doing the actual select it is able to do the index > query. But for some reason with a prepared statement, it is not able to > do it. This isn't a "can't do it" situation, it's a "doesn't want to do it" situation, and it's got nothing whatever to do with null or not null. The issue is the estimated row count, which in the first case is so high as to make the seqscan approach look cheaper. So the real question here is what are the statistics on the column that are making the planner guess such a large number when it has no specific information about the compared-to value. Do you have one extremely common value in the column? Have you done an ANALYZE recently on the table, and if so can you show us the pg_stats row for the column? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries
Tom Lane wrote: [ enlarging on Richard's response a bit ] John Meinel <[EMAIL PROTECTED]> writes: jfmeinel=> explain analyze execute myget(3); QUERY PLAN Seq Scan on tdata (cost=0.00..9773.10 rows=181923 width=4) (actual time=1047.000..1047.000 rows=0 loops=1) Filter: (project_id = $1) Total runtime: 1047.000 ms jfmeinel=> explain analyze select id from tdata where project_id = 3; QUERY PLAN - Index Scan using tdata_project_id_idx on tdata (cost=0.00..4.20 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops =1) Index Cond: (project_id = 3) Total runtime: 0.000 ms So notice that when doing the actual select it is able to do the index query. But for some reason with a prepared statement, it is not able to do it. This isn't a "can't do it" situation, it's a "doesn't want to do it" situation, and it's got nothing whatever to do with null or not null. The issue is the estimated row count, which in the first case is so high as to make the seqscan approach look cheaper. So the real question here is what are the statistics on the column that are making the planner guess such a large number when it has no specific information about the compared-to value. Do you have one extremely common value in the column? Have you done an ANALYZE recently on the table, and if so can you show us the pg_stats row for the column? regards, tom lane The answer is "yes" that particular column has very common numbers in it. Project id is a number from 1->21. I ended up modifying my query such that I do the bulk of the work in a regular UNION SELECT so that all that can be optimized, and then I later do another query for this row in an 'EXECUTE ...' so that unless I'm actually requesting a small number, the query planner can notice that it can do an indexed query. I'm pretty sure this is just avoiding worst case scenario. Because it is true that if I use the number 18, it will return 500,000 rows. Getting those with an indexed lookup would be very bad. But typically, I'm doing numbers in a very different range, and so the planner was able to know that it would not likely find that number. Thanks for pointing out what the query planner was thinking, I was able to work around it. John =:-> signature.asc Description: OpenPGP digital signature
[PERFORM] stubborn query confuses two different servers
Dear Gurus,
Here is this strange query that can't find the optimum plan unless I disable
some scan modes or change the costs.
(A) is a 2x2.4GHz server with hw raid5 and v7.3.4 database. It chooses
hashjoin.
(B) is a 300MHz server with 7200rpm ide and v7.4.2 database. It chooses
seqscan.
If I disable hashjoin/seqscan+hashjoin+mergejoin, both choose index scan.
(A) goes from 1000ms to 55ms
(B) goes from 5000+ms to 300ms
If your expert eyes could catch something missing (an index, analyze or
something), I'd be greatly honoured :) Also, tips about which optimizer
costs may be too high or too low are highly appreciated.
As far as I fumbled with (B), disabling plans step by step got worse until
after disabled all tree. Reducing random_page_cost from 2 to 1.27 or lower
instantly activated the index scan, but I fear that it hurt most of our
other queries. The faster server did not respond to any changes, even with
rpc=1 and cpu_index_tuple_cost=0.0001, it chose hash join.
All that I discovered is that both servers fail to find the right index
(szlltlvl_ttl_szlltlvl) unless forced to.
In hope of an enlightening answer,
Yours,
G.
%--- cut here ---%
-- QUERY:
explain analyze -- 5000msec. rpc1.27-: 300
SELECT coalesce(szallitolevel,0) AS scope_kov_szallitolevel,
CASE 'raktáros' WHEN 'raktáros' THEN szallitolevel_bejovo_e(szallitolevel)
WHEN 'sofőr' THEN 1027=(SELECT coalesce(sofor,0) FROM szallitolevel WHERE
az=szallitolevel) ELSE true END
FROM
(SELECT l.az AS szallitolevel
FROM szallitolevel l, szallitolevel_tetele t
WHERE szallitas=1504 AND allapot NOT IN (6,7,8)
-- pakolandó tételekkel
AND t.szallitolevel = l.az AND NOT t.archiv
-- ha archív van, de most nincs, legföljebb köv körben kibukik
AND t.fajta IN (4,90,100)
GROUP BY t.szallitolevel, l.az
HAVING count(t.*)>0) t1
NATURAL FULL OUTER JOIN
(SELECT szallitolevel, az AS pakolas FROM pakolas WHERE szallitasba=1504 AND
sztornozott_pakolas IS NULL) t2
WHERE pakolas IS NULL ORDER BY 2 DESC LIMIT 1;
%--- cut here ---%
-- plan of (A), hashjoin --
QUERY PLAN
Limit (cost=2795.58..2795.58 rows=1 width=12) (actual
time=1089.72..1089.72 rows=1 loops=1)
-> Sort (cost=2795.58..2804.26 rows=3472 width=12) (actual
time=1089.72..1089.72 rows=2 loops=1)
Sort Key: szallitolevel_bejovo_e(szallitolevel)
-> Merge Join (cost=2569.48..2591.39 rows=3472 width=12) (actual
time=1086.72..1089.67 rows=2 loops=1)
Merge Cond: ("outer".szallitolevel = "inner".szallitolevel)
Filter: ("inner".az IS NULL)
-> Sort (cost=1613.43..1614.15 rows=288 width=12) (actual
time=1054.21..1054.26 rows=80 loops=1)
Sort Key: t1.szallitolevel
-> Subquery Scan t1 (cost=1572.82..1601.65 rows=288
width=12) (actual time=1050.72..1054.09 rows=80 loops=1)
-> Aggregate (cost=1572.82..1601.65 rows=288
width=12) (actual time=1050.70..1053.93 rows=80 loops=1)
Filter: (count("*") > 0)
-> Group (cost=1572.82..1594.44 rows=2883
width=12) (actual time=1050.64..1052.98 rows=824 loops=1)
-> Sort (cost=1572.82..1580.03
rows=2883 width=12) (actual time=1050.63..1051.24 rows=824 loops=1)
Sort Key: t.szallitolevel, l.az
-> Hash Join
(cost=531.09..1407.13 rows=2883 width=12) (actual time=8.13..1048.89
rows=824 loops=1)
Hash Cond:
("outer".szallitolevel = "inner".az)
-> Index Scan using
szallitolevel_tetele_me on szallitolevel_tetele t (cost=0.00..2.25
rows=167550 width=8) (actual time=0.18..871.77 rows=167888 loops=1)
Filter: ((NOT
archiv) AND ((fajta = 4) OR (fajta = 90) OR (fajta = 100)))
-> Hash
(cost=530.06..530.06 rows=411 width=4) (actual time=7.92..7.92 rows=0
loops=1)
-> Index Scan
using szlltlvl_szllts on szallitolevel l (cost=0.00..530.06 rows=411
width=4) (actual time=0.04..7.81 rows=92 loops=1)
Index Cond:
(szallitas = 1504)
Filter:
((allapot <> 6) AND (allapot <> 7) AND (allapot <> 8))
-> Sort (cost=956.05..964.73 rows=3472 width=8) (actual
time=27.80..30.24 rows=3456 loops=1)
Sort Key: pakolas.szallitolevel
-> Index Scan using pakol
[PERFORM] index not used when using function
Hi all, a small question: I've got this table "songs" and an index on column artist. Since there's about one distinct artist for every 10 rows, it would be nice if it could use this index when counting artists. It doesn't however: lyrics=> EXPLAIN ANALYZE SELECT count(DISTINCT artist) FROM songs; Aggregate (cost=31961.26..31961.26 rows=1 width=14) (actual time=808.863..808.864 rows=1 loops=1) -> Seq Scan on songs (cost=0.00..31950.41 rows=4341 width=14) (actual time=26.801..607.172 rows=25207 loops=1) Total runtime: 809.106 ms Even with enable_seqscan to off, it just can't seem to use the index. The same query without the count() works just fine: lyrics=> EXPLAIN ANALYZE SELECT DISTINCT artist FROM songs; Unique (cost=0.00..10814.96 rows=828 width=14) (actual time=0.029..132.903 rows=3280 loops=1) -> Index Scan using songs_artist_key on songs (cost=0.00..10804.11 rows=4341 width=14) (actual time=0.027..103.448 rows=25207 loops=1) Total runtime: 135.697 ms Of course I can just take the number of rows from the latter query, but I'm still wondering why it can't use indexes with functions. Thanks -- Shiar - http://www.shiar.org > Faktoj estas malamik del verajh ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Interest in perf testing?
Josh Berkus wrote: > Folks, > > I'm beginning a series of tests on OSDL's Scalable Test Platform in order to > determine some recommended settings for many of the new PostgreSQL.conf > parameters as well as pg_autovacuum. > > Is anyone else interested in helping me with this? > What do you need ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] O_DIRECT setting
On Thu, Sep 23, 2004 at 10:57:41AM -0400, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > TODO has: > > * Consider use of open/fcntl(O_DIRECT) to minimize OS caching > > Should the item be removed? > > I think it's fine ;-) ... it says "consider it", not "do it". The point > is that we could do with more research in this area, even if O_DIRECT > per se is not useful. Maybe you could generalize the entry to > "investigate ways of fine-tuning OS caching behavior". > > regards, tom lane > I talked to Jan a little about this during OSCon since Linux filesystems (ext2, ext3, etc) let you use O_DIRECT. He felt the only place where PostgreSQL may benefit from this now, without managing its own buffer first, would be with the log writer. I'm probably going to get this wrong, but he thought it would be interesting to try an experiment by taking X number of pages to be flushed, sort them (by age? where they go on disk?) and write them out. He thought this would be a relatively easy thing to try, a day or two of work. We'd really love to experiment with it. Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] O_DIRECT setting
Mark Wong <[EMAIL PROTECTED]> writes: > I talked to Jan a little about this during OSCon since Linux filesystems > (ext2, ext3, etc) let you use O_DIRECT. He felt the only place where > PostgreSQL may benefit from this now, without managing its own buffer first, > would be with the log writer. I'm probably going to get this wrong, but > he thought it would be interesting to try an experiment by taking X number > of pages to be flushed, sort them (by age? where they go on disk?) and > write them out. Hmm. Most of the time the log writer has little choice about page write order --- certainly if all your transactions are small it's not going to have any choice. I think this would mainly be equivalent to O_SYNC with the extra feature of stopping the kernel from buffering the WAL data in its own buffer cache. Which is probably useful, but I doubt it's going to make a huge difference. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
