Re: [PERFORM] Create and drop temp table in 8.3.4
Seems like this didn't make it through to the list the first time... * Aidan Van Dyk [EMAIL PROTECTED] [081106 22:19]: * David Rees [EMAIL PROTECTED] [081106 21:22]: 2. A write barrier instructs the lower level hardware that commands issued before the barrier must be written to disk before commands issued after the barrier. Write barriers are used to ensure that data written to disk is written in such a way as to maintain filesystem consistency, without losing all the benefits of a write cache. 3. A fsync call forces data to be synced to the controller. This means that whenever you call fsync, at the very minimum, the data will have made it to the controller. How much further down the line will depend on whether or not the controller is in WriteBack or WriteThrough mode and whether or not the disk is also caching writes. So in your example, if the OS is caching some writes and fsync is called, it won't be returned until at a minimum the controller has accepted all the data, regardless of whether or not write barriers are enabled. In theory, it should be safe to disable write barriers if you have a BBU because the BBU should guarantee that all writes will eventually make it to disk (or at least reduce the risk of that not happening to an acceptable level). All that's correct, but note that fsync doesn't guarentee *coherent* filesystem state has been made to controller. And fsync *can* carry later writes to the controller. I belive the particular case the prompted the write-barriers to become default was ext3 + journals, where in certain (rare) cases, upon recovery, things were out of sync. What was happening was that ext3 was syncing the journal, but extra writes were getting carried to the controller during the sync operation, and if something crashed at the right time, new data was on the disk where the old journal (because the new journal hadn't finished making it to the controller) didn't expect it. The write barriers give the FS the symantics to say all previous queue writes [BARRIER] flush to controller [BARRIER] any new writes, and thus guarentee the ordering of certian operations to disk, and guarentee coherency of the FS at all times. Of course, that guarenteed FS consistency comes at a cost. As to it's necessity with the way PG uses the FS w/ WAL or it's necessity with xfs... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [PERFORM] Oddity with view (now with test case)
On Nov 10, 2008, at 9:20 PM, Tom Lane wrote: Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes: On Nov 10, 2008, at 1:31 PM, Tom Lane wrote: On my machine this runs about twice as fast as the original view. Am I missing some magic? I'm still getting the subquery scan. Hmm, I'm getting a core dump :-( ... this seems to be busted in HEAD. 8.3 gets it right though. Doesn't seem to for me... :/ [EMAIL PROTECTED] select version(); version - PostgreSQL 8.3.5 on i386-apple-darwin8.11.1, compiled by GCC i686- apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370) (1 row) Time: 0.250 ms [EMAIL PROTECTED] explain select count(*) from v2; QUERY PLAN -- Aggregate (cost=279184.19..279184.20 rows=1 width=0) - Append (cost=0.00..254178.40 rows=10002315 width=0) - Subquery Scan *SELECT* 1 (cost=0.00..254058.50 rows=1175 width=0) - Seq Scan on a (cost=0.00..154056.75 rows=1175 width=14) - Subquery Scan *SELECT* 2 (cost=37.67..119.90 rows=2140 width=0) - Hash Join (cost=37.67..98.50 rows=2140 width=40) Hash Cond: (b.c_id = c.c_id) - Seq Scan on b (cost=0.00..31.40 rows=2140 width=8) - Hash (cost=22.30..22.30 rows=1230 width=36) - Seq Scan on c (cost=0.00..22.30 rows=1230 width=36) (10 rows) Time: 0.923 ms [EMAIL PROTECTED] \d v2 View public.v2 Column | Type | Modifiers +-+--- a | integer | b | text| c_id | integer | c_text | text| View definition: SELECT a.a, a.b, NULL::integer AS c_id, NULL::text AS c_text FROM a UNION ALL SELECT b.a, NULL::text AS b, b.c_id, c.c_text FROM b JOIN c ON b.c_id = c.c_id; [EMAIL PROTECTED] -- Decibel! [EMAIL PROTECTED] (512) 569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Oddity with view (now with test case)
Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes: On Nov 10, 2008, at 9:20 PM, Tom Lane wrote: 8.3 gets it right though. Doesn't seem to for me... :/ Oh, I was looking at select * from v2 not select count(*) from v2. HEAD is a bit smarter about the latter than 8.3 is. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Using index for IS NULL query
Index is not used for is null condition: create index makse_dokumnr_idx on makse(dokumnr); explain select sum( summa) from MAKSE where dokumnr is null Aggregate (cost=131927.95..131927.96 rows=1 width=10) - Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10) Filter: (dokumnr IS NULL) Table makse contains 120 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Andrus. PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Andrus [EMAIL PROTECTED] writes: Index is not used for is null How to fix ? Update to something newer than 8.1 (specifically, you'll need 8.3). regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Andrus [EMAIL PROTECTED] schrieb: Index is not used for is null condition: create index makse_dokumnr_idx on makse(dokumnr); explain select sum( summa) from MAKSE where dokumnr is null Aggregate (cost=131927.95..131927.96 rows=1 width=10) - Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10) Filter: (dokumnr IS NULL) Table makse contains 120 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Create a partial index like below: test=# create table foo ( i float); CREATE TABLE Zeit: 1,138 ms test=*# insert into foo select random() from generate_series(1,100); INSERT 0 100 test=*# insert into foo values (NULL); INSERT 0 1 test=*# create index idx_foo on foo(i) where i is null; CREATE INDEX test=*# explain analyse select * from foo where i is null; QUERY PLAN --- Bitmap Heap Scan on foo (cost=5.51..4690.89 rows=5000 width=8) (actual time=0.037..0.038 rows=1 loops=1) Recheck Cond: (i IS NULL) - Bitmap Index Scan on idx_foo (cost=0.00..4.26 rows=5000 width=0) (actual time=0.033..0.033 rows=1 loops=1) Index Cond: (i IS NULL) Total runtime: 0.068 ms (5 Zeilen) Maybe there are other solutions... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Tom Lane [EMAIL PROTECTED] schrieb: Andrus [EMAIL PROTECTED] writes: Index is not used for is null How to fix ? Update to something newer than 8.1 (specifically, you'll need 8.3). Right. For my example in the other mail: test=*# create index idx_foo on foo(i); CREATE INDEX test=*# explain analyse select * from foo where i is null; QUERY PLAN Bitmap Heap Scan on foo (cost=95.11..4780.49 rows=5000 width=8) (actual time=0.052..0.053 rows=1 loops=1) Recheck Cond: (i IS NULL) - Bitmap Index Scan on idx_foo (cost=0.00..93.86 rows=5000 width=0) (actual time=0.047..0.047 rows=1 loops=1) Index Cond: (i IS NULL) Total runtime: 0.076 ms Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Index is not used for is null condition: create index makse_dokumnr_idx on makse(dokumnr); explain select sum( summa) from MAKSE where dokumnr is null Aggregate (cost=131927.95..131927.96 rows=1 width=10) - Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10) Filter: (dokumnr IS NULL) Table makse contains 120 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Yes, NULL values are not stored in the index, but you may create functional index on (CASE WHEN dokumnr IS NULL THEN -1 ELSE dokumnr END) and then use the same expression in the WHERE clause. You may replace the '-1' value by something that's not used in the dokumnr column. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Create and drop temp table in 8.3.4
On Thu, 6 Nov 2008, Scott Marlowe wrote: Without write barriers, the second we call an fsync it returns true. But that's assuming write barriers work as I understand them. Write barriers do not work as you understand them. Calling fsync always blocks until all the data has made it to safe storage, and always has (barring broken systems). Write barriers are meant to be a way to speed up fsync-like operations. Before write barriers, all the system could do was call fsync, and that would cause the operating system to wait for a response from the disc subsystem that the data had been written before it could start writing some more stuff. Write barriers provide an extra way of telling the disc Write everything before the barrier before you write anything after the barrier, which allows the operating system to keep stuffing data into the disc queue without having to wait for a response. So fsync should always work right, unless the system is horribly broken, on all systems going back many years. Matthew -- I'd try being be a pessimist, but it probably wouldn't work anyway. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Oddity with view (now with test case)
On Nov 11, 2008, at 1:15 PM, Tom Lane wrote: Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes: On Nov 10, 2008, at 9:20 PM, Tom Lane wrote: 8.3 gets it right though. Doesn't seem to for me... :/ Oh, I was looking at select * from v2 not select count(*) from v2. HEAD is a bit smarter about the latter than 8.3 is. So here's something odd... in both 8.3 and HEAD from a while ago it gives a better plan for SELECT * than for SELECT count(*): [EMAIL PROTECTED] explain analyze select * from v2; QUERY PLAN - Result (cost=0.00..254178.40 rows=10002315 width=72) (actual time=0.049..8452.152 rows=999 loops=1) - Append (cost=0.00..254178.40 rows=10002315 width=72) (actual time=0.048..5887.025 rows=999 loops=1) - Seq Scan on a (cost=0.00..154056.75 rows=1175 width=14) (actual time=0.048..4207.482 rows=999 loops=1) - Hash Join (cost=37.67..98.50 rows=2140 width=40) (actual time=0.002..0.002 rows=0 loops=1) Hash Cond: (b.c_id = c.c_id) - Seq Scan on b (cost=0.00..31.40 rows=2140 width=8) (actual time=0.000..0.000 rows=0 loops=1) - Hash (cost=22.30..22.30 rows=1230 width=36) (never executed) - Seq Scan on c (cost=0.00..22.30 rows=1230 width=36) (never executed) Total runtime: 9494.162 ms (9 rows) [EMAIL PROTECTED] explain analyze select count(*) from v2; QUERY PLAN - Aggregate (cost=279184.19..279184.20 rows=1 width=0) (actual time=13155.524..13155.524 rows=1 loops=1) - Append (cost=0.00..254178.40 rows=10002315 width=0) (actual time=0.045..11042.562 rows=999 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..254058.50 rows=1175 width=0) (actual time=0.045..8976.352 rows=999 loops=1) - Seq Scan on a (cost=0.00..154056.75 rows=1175 width=14) (actual time=0.045..5936.930 rows=999 loops=1) - Subquery Scan *SELECT* 2 (cost=37.67..119.90 rows=2140 width=0) (actual time=0.002..0.002 rows=0 loops=1) - Hash Join (cost=37.67..98.50 rows=2140 width=40) (actual time=0.002..0.002 rows=0 loops=1) Hash Cond: (b.c_id = c.c_id) - Seq Scan on b (cost=0.00..31.40 rows=2140 width=8) (actual time=0.001..0.001 rows=0 loops=1) - Hash (cost=22.30..22.30 rows=1230 width=36) (never executed) - Seq Scan on c (cost=0.00..22.30 rows=1230 width=36) (never executed) Total runtime: 13155.642 ms (11 rows) [EMAIL PROTECTED] explain analyze select count(*) from (select * from v2 offset 0) a; QUERY PLAN - Aggregate (cost=379207.34..379207.35 rows=1 width=0) (actual time=12592.273..12592.274 rows=1 loops=1) - Limit (cost=0.00..254178.40 rows=10002315 width=72) (actual time=0.173..11057.717 rows=999 loops=1) - Result (cost=0.00..254178.40 rows=10002315 width=72) (actual time=0.172..9213.524 rows=999 loops=1) - Append (cost=0.00..254178.40 rows=10002315 width=72) (actual time=0.172..6608.656 rows=999 loops=1) - Seq Scan on a (cost=0.00..154056.75 rows=1175 width=14) (actual time=0.171..4793.116 rows=999 loops=1) - Hash Join (cost=37.67..98.50 rows=2140 width=40) (actual time=0.002..0.002 rows=0 loops=1) Hash Cond: (b.c_id = c.c_id) - Seq Scan on b (cost=0.00..31.40 rows=2140 width=8) (actual time=0.001..0.001 rows=0 loops=1) - Hash (cost=22.30..22.30 rows=1230 width=36) (never executed) - Seq Scan on c (cost=0.00..22.30 rows=1230 width=36) (never executed) Total runtime: 12592.442 ms (11 rows) And yes, explain overhead is huge... [EMAIL PROTECTED] \timing Timing is on. [EMAIL PROTECTED] select count(*) from v2; count - 999 (1 row) Time: 6217.624 ms [EMAIL PROTECTED] -- Decibel! [EMAIL PROTECTED] (512) 569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Yes, NULL values are not stored in the index, but you may create functional index on Are you sure NULL values are not stored? btree, gist and bitmap index and search for NULL values. select amname, amindexnulls, amsearchnulls from pg_am; amname | amindexnulls | amsearchnulls +--+--- btree | t| t hash | f| f gist | t| t gin| f| f bitmap | t| t (5 rows) Sincerely yours, Vladimir Sitnikov
Re: [PERFORM] Using index for IS NULL query
On Tue, Nov 11, 2008 at 4:00 PM, Vladimir Sitnikov [EMAIL PROTECTED] wrote: Yes, NULL values are not stored in the index, but you may create functional index on Are you sure NULL values are not stored? btree, gist and bitmap index and search for NULL values. It's not that they're not stored, it's that before 8.3 pg didn't know how to compare to them I believe. The standard trick was to create a partial index with where x is null on the table / column. 8.3 knows how to compare them and doesn't need the partial index. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Oddity with view (now with test case)
Jim 'Decibel!' Nasby [EMAIL PROTECTED] writes: So here's something odd... in both 8.3 and HEAD from a while ago it gives a better plan for SELECT * than for SELECT count(*): The short answer is that the Subquery Scan nodes can be dropped out when they are no-ops, which is to say producing the same set of columns their input produces (and not testing any filter conditions, but that's not relevant here). SELECT count(*) doesn't want to know about any columns so the output of the UNION arm doesn't match ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
