Re: [PERFORM] Postgres Optimizer is not smart enough?
Litao Wu wrote:
Hi All,
Here is my test comparison between Postgres (7.3.2)
optimizer vs Oracle (10g) optimizer.
It seems to me that Postgres optimizer is not smart
enough.
Did I miss anything?
Yeah, 7.4.
7.3.2 is *ancient*. Here's output from 7.4:
[EMAIL PROTECTED] explain analyze
test-# SELECT module, sum(action_deny)
test-# FROM test
test-# WHERE created = ('now'::timestamptz - '1
test'# day'::interval) AND customer_id='100'
test-# AND domain='100'
test-# GROUP BY module;
QUERY PLAN
---
HashAggregate (cost=5.69..5.69 rows=1 width=13) (actual
time=715.058..715.060 rows=1 loops=1)
- Index Scan using test_id1 on test (cost=0.00..5.68 rows=1
width=13) (actual time=0.688..690.459 rows=1 loops=1)
Index Cond: ((customer_id = 100) AND (created = '2005-01-11
17:52:22.364145-05'::timestamp with time zone) AND ((domain)::text =
'100'::text))
Total runtime: 717.546 ms
(4 rows)
[EMAIL PROTECTED] create index test_id2 on test(domain);
CREATE INDEX
[EMAIL PROTECTED] analyze test;
ANALYZE
[EMAIL PROTECTED]
[EMAIL PROTECTED] explain analyze
test-# SELECT module, sum(action_deny)
test-# FROM test
test-# WHERE created = ('now'::timestamptz - '1
test'# day'::interval) AND customer_id='100'
test-# AND domain='100'
test-# GROUP BY module;
QUERY PLAN
HashAggregate (cost=5.68..5.69 rows=1 width=13) (actual
time=10.778..10.780 rows=1 loops=1)
- Index Scan using test_id2 on test (cost=0.00..5.68 rows=1
width=13) (actual time=10.702..10.721 rows=1 loops=1)
Index Cond: ((domain)::text = '100'::text)
Filter: ((created = '2005-01-11
17:53:16.720749-05'::timestamp with time zone) AND (customer_id = 100))
Total runtime: 11.039 ms
(5 rows)
[EMAIL PROTECTED] select version();
PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.0 20040204 (prerelease)
(1 row)
Hope that helps,
Mike Mascari
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Low Performance for big hospital server ..
William Yu wrote: [EMAIL PROTECTED] wrote: Yes , vacuumdb daily. Do you vacuum table by table or the entire DB? I find over time, the system tables can get very bloated and cause a lot of slowdowns just due to schema queries/updates. You might want to try a VACUUM FULL ANALYZE just on the system tables. A REINDEX of the system tables in stand-alone mode might also be in order, even for a 7.4.x database: http://www.postgresql.org/docs/7.4/interactive/sql-reindex.html If a dump-reload-analyze cycle yields significant performance improvements then we know it's due to dead-tuple bloat - either heap tuples or index tuples. Mike Mascari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Slow execution time when querying view with WHERE clause
Tom Lane wrote: Um ... doh ... analyze.c about line 1550: /* We can only compute valid stats if we found some non-null values. */ if (nonnull_cnt 0) ... There's a bit of an epistemological issue here: if we didn't actually find any nonnull values in our sample, is it legitimate to assume that the column is entirely null? On the other hand, if we find only 3 in our sample we will happily assume the column contains only 3, so I dunno why we are discriminating against null. This seems like a case that just hasn't come up before. Will this discriminatory policy toward null end for 8.0? Mike Mascari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Slow execution time when querying view with WHERE clause
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: When I query the view with a simple filter, I get: explain analyze select * from p_areas where deactive is null; The problem seems to be here: - Seq Scan on _areas a (cost=0.00..2.48 rows=1 width=163) (actual time=0.037..0.804 rows=48 loops=1) Filter: (deactive IS NULL) Why is it so completely off about the selectivity of the IS NULL clause? Are you sure you ANALYZEd this table recently? Yes. I just did: [EMAIL PROTECTED] vacuum full analyze; VACUUM [EMAIL PROTECTED] explain analyze select * from p_areas where deactive is null; QUERY PLAN -- Nested Loop IN Join (cost=8.62..512.47 rows=1 width=162) (actual time=1143.969..21811.417 rows=37 loops=1) Join Filter: (outer.area = inner.area) - Seq Scan on _areas a (cost=0.00..2.49 rows=1 width=162) (actual time=0.037..1.673 rows=49 loops=1) Filter: (deactive IS NULL) - Nested Loop (cost=8.62..25740.20 rows=2681 width=8) (actual time=1.172..429.501 rows=3566 loops=49) - Nested Loop (cost=8.62..16674.93 rows=2680 width=8) (actual time=1.125..281.570 rows=3566 loops=49) - Merge Join (cost=8.62..3012.72 rows=2778 width=8) (actual time=0.876..128.908 rows=3566 loops=49) Merge Cond: (outer.pricegroup = inner.pricegroup) - Nested Loop IN Join (cost=8.62..1929.41 rows=9 width=8) (actual time=0.613..5.504 rows=9 loops=49) Join Filter: (outer.buyer = inner.store) - Index Scan using i_pricemembers3 on _pricemembers p (cost=0.00..11.13 rows=217 width=16) (actual time=0.403..1.476 rows=142 loops=49) - Subquery Scan IN_subquery (cost=8.62..8.74 rows=8 width=8) (actual time=0.013..0.019 rows=1 loops=6950) - Unique (cost=8.62..8.66 rows=8 width=8) (actual time=0.007..0.010 rows=1 loops=6950) - Sort (cost=8.62..8.64 rows=8 width=8) (actual time=0.003..0.004 rows=1 loops=6950) Sort Key: store - Append (cost=2.87..8.50 rows=8 width=8) (actual time=8.394..8.446 rows=1 loops=1) - Subquery Scan *SELECT* 1 (cost=2.87..5.17 rows=5 width=8) (actual time=8.112..8.112 rows=0 loops=1) - Hash Join (cost=2.87..5.12 rows=5 width=8) (actual time=8.106..8.106 rows=0 loops=1) Hash Cond: (outer.company = inner.company) - Seq Scan on _stores s (cost=0.00..2.13 rows=13 width=16) (actual time=0.014..0.052 rows=13 loops=1) - Hash (cost=2.87..2.87 rows=1 width=8) (actual time=7.878..7.878 rows=0 loops=1) - Seq Scan on _webusers w (cost=0.00..2.87 rows=1 width=8) (actual time=7.868..7.868 rows=0 loops=1) Filter: (webuser = getwebuser()) - Subquery Scan *SELECT* 2 (cost=1.08..3.33 rows=3 width=8) (actual time=0.273..0.322 rows=1 loops=1) - Hash Join (cost=1.08..3.30 rows=3 width=8) (actual time=0.263..0.308 rows=1 loops=1) Hash Cond: (outer.company = inner.company) - Seq Scan on _stores s (cost=0.00..2.13 rows=13 width=16) (actual time=0.008..0.042 rows=13 loops=1) - Hash (cost=1.07..1.07 rows=1 width=8) (actual time=0.093..0.093 rows=0 loops=1) - Seq Scan on _companies c (cost=0.00..1.07 rows=1 width=8) (actual time=0.061..0.081 rows=1 loops=1) Filter: ((companyid)::text = 'DEFAULT'::text) - Index Scan using i_offers4 on _offers o (cost=0.00..1014.76 rows=16298 width=16) (actual time=0.244..72.742 rows=10433 loops=49) - Index Scan using i_inventories1 on _inventories i (cost=0.00..4.91 rows=1 width=16) (actual time=0.025..0.029 rows=1 loops=174715) Index Cond: (i.inventory = outer.inventory) - Index Scan using i_bins1 on _bins b (cost=0.00..3.37
[PERFORM] Slow execution time when querying view with WHERE clause
= inner.company) - Seq Scan on _stores s (cost=0.00..2.13 rows=13 width=16) (actual time=0.008..0.080 rows=13 loops=1) - Hash (cost=1.07..1.07 rows=1 width=8) (actual time=0.116..0.116 rows=0 loops=1) - Seq Scan on _companies c (cost=0.00..1.07 rows=1 width=8) (actual time=0.062..0.083 rows=1 loops=1) Filter: ((companyid)::text = 'DEFAULT'::text) - Index Scan using i_offers4 on _offers o (cost=0.00..1007.93 rows=15524 width=16) (actual time=0.023..67.183 rows=10049 loops=48) - Index Scan using i_inventories1 on _inventories i (cost=0.00..5.07 rows=1 width=16) (actual time=0.025..0.029 rows=1 loops=163561) Index Cond: (i.inventory = outer.inventory) - Index Scan using i_bins1 on _bins b (cost=0.00..3.40 rows=1 width=16) (actual time=0.021..0.026 rows=1 loops=163561) Index Cond: (b.bin = outer.bin) Total runtime: 20027.414 ms (36 rows) --- That's a slow-down on execution time by a factor of 50, even though the row count was the same: 34. In fact, it's MUCH faster to do: create temporary table foo as select * from p_areas; select * from foo where deactive is null; The database has been analyzed. Any tips would be greatly appreciated. Mike Mascari P.S.: I turned off word-wrap in my mail client for this post. Is that the right thing to do for analyze output? ---(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] Slow execution time when querying view with WHERE clause
Mike Mascari wrote: I have the following view: create or replace view market.p_areas as select a.* from _areas a where a.area in ( select b.area from _bins b, _inventories i, _offers o, _pricemembers p where b.bin = i.bin and i.inventory = o.inventory and o.pricegroup = p.pricegroup and p.buyer in ( select s.store from _stores s, _webusers w where w.webuser = getWebuser() and w.company = s.company union select s.store from _stores s, _companies c where s.company = c.company and c.companyid = 'DEFAULT' ) ); ... I failed to report the version: select version(); PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.0 20040204 (prerelease) Sorry. Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
