On Mon, Jan 23, 2012 at 11:28 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Maxim Boguk <maxim.bo...@gmail.com> writes: > > But it seems that index scan cost for very narrow/selective conditional > > indexes is greatly overestimated at least in some cases. > > I realized in connection with > http://archives.postgresql.org/pgsql-general/2012-01/msg00459.php > that btcostestimate is not correctly estimating numIndexTuples for > partial indexes. But it's impossible to tell from this amount of > information whether you're seeing an effect of that, or something else. > Can you provide a self-contained test case? > > regards, tom lane > Prorably simpliest test case: set random_page_cost to 4; set seq_page_cost to 1; drop table if exists test; CREATE TABLE test (id integer primary key, value1 float, value2 float, value3 float, value4 float); INSERT into test select id,random() as value1,random() as value2, random() as value3,random() as value4 from generate_series(1,1000000) as g(id); CREATE INDEX test_special_key on test(value1) where value2*2<0.01 and value3*2<0.01 and value4*2<0.01; ANALYZE test; postgres=# EXPLAIN ANALYZE select * from test order by id limit 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3.43 rows=100 width=36) (actual time=0.042..0.170 rows=100 loops=1) -> Index Scan using test_pkey on test (cost=0.00..34317.36 rows=1000000 width=36) (actual time=0.040..0.108 rows=100 loops=1) Total runtime: 0.243 ms (3 rows) vs postgres=# EXPLAIN ANALYZE select * from test where value2*2<0.01 and value3*2<0.01 and value4*2<0.01 order by value1 limit 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..92.52 rows=100 width=36) (actual time=0.072..0.072 rows=0 loops=1) -> Index Scan using test_special_key on test (cost=0.00..34264.97 rows=37037 width=36) (actual time=0.070..0.070 rows=0 loops=1) Total runtime: 0.113 ms (3 rows) cost difference: (cost=0.00..3.43 rows=100 width=36) vs (cost=0.00..92.52 rows=100 width=36) An actual speed (and theoretical performance) almost same. More selective conditions added to conditional index - worse situation with wrong costing. Kind Regards, Maksym -- Maxim Boguk Senior Postgresql DBA. Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.bo...@gmail.com LinkedIn profile: http://nz.linkedin.com/in/maximboguk "If they can send one man to the moon... why can't they send them all?" МойКруг: http://mboguk.moikrug.ru/ "People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
