Hi, i've ran into a planning problem.
Dedicated PostgreSQL Server: "PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit" Memory: 8GB 4CPUs The problem is reduced to the following: there are 2 tables: -product (3millions rows, 1GB) -product_parent (3000rows, 0.5MB) If effective_cache_size has a greater value (6GB), this select has a bad planning and long query time (2000ms): select distinct product_code from product p_ inner join product_parent par_ on p_.parent_id=par_.id where par_.parent_name like 'aa%' limit 2 If effective_cache_size is smaller (32MB), planning is ok and query is fast. (10ms) In the worst case (effective_cache_size=6GB) the speed depends on the value of 'limit' (in select): if it is smaller, query is slower. (12ms) Good planning: http://explain.depesz.com/s/0FD "Limit (cost=3704.00..3704.02 rows=2 width=5) (actual time=0.215..0.217 rows=1 loops=1)" " -> HashAggregate (cost=3704.00..3712.85 rows=885 width=5) (actual time=0.213..0.215 rows=1 loops=1)" " -> Nested Loop (cost=41.08..3701.79 rows=885 width=5) (actual time=0.053..0.175 rows=53 loops=1)" " -> Index Scan using telepulesbugreport_nev_idx on product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1)" " Index Cond: (((parent_name)::text ~>=~ 'aa'::text) AND ((parent_name)::text ~<~ 'ab'::text))" " Filter: ((parent_name)::text ~~ 'aa%'::text)" " -> Bitmap Heap Scan on product p_ (cost=41.08..3680.59 rows=1034 width=9) (actual time=0.033..0.125 rows=53 loops=1)" " Recheck Cond: (parent_id = par_.id)" " -> Bitmap Index Scan on kapubugreport_telepules_id_idx (cost=0.00..40.82 rows=1034 width=0) (actual time=0.024..0.024 rows=53 loops=1)" " Index Cond: (parent_id = par_.id)" "Total runtime: 0.289 ms" Bad planning: http://explain.depesz.com/s/yBh "Limit (cost=0.00..854.37 rows=2 width=5) (actual time=1799.209..4344.041 rows=1 loops=1)" " -> Unique (cost=0.00..378059.84 rows=885 width=5) (actual time=1799.207..4344.038 rows=1 loops=1)" " -> Nested Loop (cost=0.00..378057.63 rows=885 width=5) (actual time=1799.204..4344.020 rows=53 loops=1)" " Join Filter: (p_.parent_id = par_.id)" " -> Index Scan using kapubugreport_irsz_telepules_id_idx on product p_ (cost=0.00..334761.59 rows=2885851 width=9) (actual time=0.015..1660.449 rows=2884172 loops=1)" " -> Materialize (cost=0.00..8.27 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=2884172)" " -> Index Scan using telepulesbugreport_nev_idx on product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)" " Index Cond: (((parent_name)::text ~>=~ 'aa'::text) AND ((parent_name)::text ~<~ 'ab'::text))" " Filter: ((parent_name)::text ~~ 'aa%'::text)" "Total runtime: 4344.083 ms" schema: CREATE TABLE product ( id serial NOT NULL, parent_id integer NOT NULL, product_code character varying COLLATE pg_catalog."C" NOT NULL, product_name character varying NOT NULL ) WITH ( OIDS=FALSE ); ALTER TABLE product OWNER TO aa; CREATE INDEX product_code_parent_id_idx ON product USING btree (product_code COLLATE pg_catalog."C" , parent_id ); CREATE INDEX product_name_idx ON product USING btree (product_name COLLATE pg_catalog."default" ); CREATE INDEX product_parent_id_idx ON product USING btree (parent_id ); CREATE INDEX product_parent_id_ocde_idx ON product USING btree (parent_id , product_code COLLATE pg_catalog."C" ); CREATE TABLE product_parent ( id serial NOT NULL, parent_name character varying NOT NULL, CONSTRAINT telepulesbugreport_pkey PRIMARY KEY (id ) ) WITH ( OIDS=FALSE ); ALTER TABLE product_parent OWNER TO aa; CREATE INDEX product_parent_name_idx ON product_parent USING btree (parent_name COLLATE pg_catalog."default" varchar_pattern_ops); I hope you can help me... :) Best Regads, Istvan
