The following bug has been logged on the website:
Bug reference: 7593
Logged by: DE MURCIA Julien
Email address: demur...@ockham-solutions.fr
PostgreSQL version: 9.2.1
Operating system: Windows 7
Description:
Hello,
I am trying to run this query:
select n.numero_id, h.service_id, h.groupe_id
from identite i, numero n, habilitation h
where
(h.service_id=100 or h.groupe_id=50)
and n.numero_id in (25393, 25384)
and i.procedure_id=h.procedure_id
and i.numero_id=n.numero_id;
At first I get 4 results (as expected) with this plan:
"Nested Loop (cost=15.99..73.40 rows=1 width=24)"
" Join Filter: (i.procedure_id = h.procedure_id)"
" -> Hash Join (cost=15.99..46.76 rows=1 width=16)"
" Hash Cond: (i.numero_id = n.numero_id)"
" -> Seq Scan on identite i (cost=0.00..25.10 rows=1510 width=16)"
" -> Hash (cost=15.96..15.96 rows=2 width=8)"
" -> Bitmap Heap Scan on numero n (cost=8.55..15.96 rows=2
width=8)"
" Recheck Cond: (numero_id = ANY
('{25393,25384}'::bigint[]))"
" -> Bitmap Index Scan on numero_pkey (cost=0.00..8.55
rows=2 width=0)"
" Index Cond: (numero_id = ANY
('{25393,25384}'::bigint[]))"
" -> Seq Scan on habilitation h (cost=0.00..26.50 rows=11 width=24)"
" Filter: ((service_id = 100) OR (groupe_id = 50))"
After a little while (probably after the analyze), the query plan is changed
and only 2 results are returned !
"Merge Join (cost=33.31..39.69 rows=1 width=24)"
" Merge Cond: (i.numero_id = n.numero_id)"
" -> Sort (cost=33.31..33.35 rows=17 width=24)"
" Sort Key: i.numero_id"
" -> Hash Join (cost=26.64..32.96 rows=17 width=24)"
" Hash Cond: (i.procedure_id = h.procedure_id)"
" -> Seq Scan on identite i (cost=0.00..5.02 rows=302
width=16)"
" -> Hash (cost=26.50..26.50 rows=11 width=24)"
" -> Seq Scan on habilitation h (cost=0.00..26.50
rows=11 width=24)"
" Filter: ((service_id = 100) OR (groupe_id =
50))"
" -> Index Only Scan using numero_pkey on numero n (cost=0.00..12.57
rows=2 width=8)"
" Index Cond: (numero_id = ANY ('{25393,25384}'::bigint[]))"
Below is the script that creates the test data:
CREATE TABLE numero
(
numero_id bigint NOT NULL,
numero character varying(50) NOT NULL,
primary key (numero_id)
);
CREATE TABLE identite
(
identite_id bigint NOT NULL,
numero_id bigint NOT NULL,
procedure_id bigint NOT NULL,
primary key (identite_id)
);
CREATE TABLE habilitation
(
habilitation_id bigint NOT NULL,
procedure_id bigint NOT NULL,
type integer,
groupe_id bigint,
service_id bigint,
utilisateur_id bigint,
primary key (habilitation_id)
);
CREATE OR REPLACE FUNCTION FILL_WITH_FAKE_DATA() RETURNS VARCHAR AS $$
declare
i bigint;
begin
for i in 1..35000 loop
insert into numero(numero_id,numero) values (i, '123');
end loop;
for i in 1..300 loop
insert into identite(identite_id,numero_id,procedure_id ) values
(i,i,1);
end loop;
for i in 1..30 loop
insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (i,2,50,null);
end loop;
return ' FILL_WITH_FAKE_DATA DONE';
end;
$$ LANGUAGE plpgsql;
select FILL_WITH_FAKE_DATA();
drop FUNCTION FILL_WITH_FAKE_DATA();
insert into identite(identite_id,numero_id,procedure_id ) values
(25393,25393,1);
insert into identite(identite_id,numero_id,procedure_id ) values
(25384,25384,1);
insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (31,1,50,null);
insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (32,1,null,100);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
