Re: BUG #3778: Natural join with filter problem

Laurent HERVE wrote:
> The join is correctly done when i do not use the numero_releve='2006-10'
> filter. But when i use it, the join is not working. The explain plan gives
> me a information about why :
> "Index Scan using document_operation_fk_idx"
> but for me it seems like a bug ...

I don't see anything wrong here. How exactly is the join not working?
Are you getting unexpected results? What are you getting and what did
you expect to happen?

> Following all details :
> \d lignes_operation
>
> Table «
> public.lignes_operation »
> Colonne | Type |
> Modificateurs
> --------------------------------------+-----------------------+-------------
> ---------------------------------------------------------------
> code_banque | character varying(9) | not null
> numero_guichet | character varying(22) | not null
> numero_compte | character varying(22) | not null
> numero_sequence | integer | not null
> default nextval('lignes_operation_numero_sequence_seq'::regclass)
> code_utilisateur | character varying(32) | not null
> code_devise | character(3) | not null
> devise_base | character(3) | not null
> date_valeur | date | not null
> code_document | integer |
> lig_code_banque | character varying(9) |
> lig_numero_guichet | character varying(22) |
> lig_numero_compte | character varying(22) |
> lig_numero_sequence | integer |
> numero_carte | character(16) |
> che_code_banque | character varying(9) |
> che_numero_guichet | character varying(22) |
> che_numero_compte | character varying(22) |
> numero_chequier | integer |
> numero_cheque | character varying(12) |
> moy_code_banque | character varying(9) | not null
> code_moyen | character(6) | not null
> debit_credit | character(1) | not null
> code_partenaire | character varying(32) | not null
> code_credit | character varying(10) |
> numero_echeance_credit | integer |
> typ_code_banque | character varying(9) | not null
> code_type_operation | character(6) | not null
> rel_code_banque | character varying(9) |
> rel_numero_guichet | character varying(22) |
> rel_numero_compte | character varying(22) |
> numero_releve | character varying(10) |
> code_ecart | integer |
> code_statut_operation | character(3) | not null
> default 'A'::bpchar
> date_operation | date | not null
> default ('now'::text)::date
> detail_operation | ezm_ldesc |
> sens_operation | character(1) | not null
> default 'D'::bpchar
> montant_operation | numeric(10,2) | not null
> default 0
> taxes_et_frais | numeric(10,2) | not null
> default 0
> montant_total | numeric(10,2) | not null
> default 0
> date_derniere_modification_operation | date | not null
> default ('now'::text)::date
> montant_base | numeric(10,2) | not null
> default 0
> taux_de_change | double precision | not null
> default 1
> date_posted | date |
> transaction_id | ezm_trid |
> reference_number | character varying(32) |
> sic | numeric(6,0) |
> hors_suivi_budget | boolean | not null
> default false
> generer_cat_partenaire | boolean | not null
> default false
> credit_montant_echeance | boolean | not null
> default false
> credit_montant_interets | boolean | not null
> default false
> credit_montant_assurance | boolean | not null
> default false
> credit_montant_autres | boolean | not null
> default false
> credit_financement | boolean | not null
> default false
> Index :
> « pk_lignes_operation » PRIMARY KEY, btree (code_banque,
> numero_guichet, numero_compte, numero_sequence), tablespace « ezm_indexes
> »
> « carte_operation_fk_idx » btree (numero_carte), tablespace «
> ezm_indexes »
> « cheque_operation_fk_idx » btree (che_code_banque,
> che_numero_guichet, che_numero_compte, numero_chequier, numero_cheque),
> tablespace « ezm_indexes »
> « compte_ligne_operation_fk_idx » btree (code_banque, numero_guichet,
> numero_compte), tablespace « ezm_indexes »
> « devise_base_operation_fk_idx » btree (code_devise), tablespace «
> ezm_indexes »
> « devise_operation_fk_idx » btree (devise_base), tablespace «
> ezm_indexes »
> « document_operation_fk_idx » btree (code_document), tablespace «
> ezm_indexes »
> « lien_operation_echeance_credit_fk_idx » btree (code_credit,
> numero_echeance_credit), tablespace « ezm_indexes »
> « ligne_operation_ecart2_fk_idx » btree (code_ecart), tablespace «
> ezm_indexes »
> « moyen_operation_fk_idx » btree (moy_code_banque, code_moyen,
> debit_credit), tablespace « ezm_indexes »
> « partenaire_operation_fk_idx » btree (code_partenaire), tablespace «
> ezm_indexes »
> « releve_operation_fk_idx » btree (rel_code_banque,
> rel_numero_guichet, rel_numero_compte, numero_releve), tablespace «
> ezm_indexes »
> « statut_ligne_operation_fk_idx » btree (code_statut_operation),
> tablespace « ezm_indexes »
> « type_ligne_operation_fk_idx » btree (typ_code_banque,
> code_type_operation), tablespace « ezm_indexes »
> « utilisateur_operation_fk_idx » btree (code_utilisateur), tablespace
> « ezm_indexes »
> « virement_operation_fk_idx » btree (lig_code_banque,
> lig_numero_guichet, lig_numero_compte, lig_numero_sequence), tablespace «
> ezm_indexes »
> Contraintes de vérification :
> « ckc_code_statut_operation_lignes_operation » CHECK
> (code_statut_operation = ANY (ARRAY['A'::bpchar, 'R'::bpchar, 'E'::bpchar,
> 'D'::bpchar, 'C'::bpchar, 'P'::bpchar, 'DEL'::bpchar]))
> « ckc_debit_credit_lignes_operation » CHECK (debit_credit = ANY
> (ARRAY['C'::bpchar, 'D'::bpchar]))
> « ckc_sens_operation_lignes_operation » CHECK (sens_operation = ANY
> (ARRAY['D'::bpchar, 'C'::bpchar]))
> « ckc_taux_de_change_lignes_operation » CHECK (taux_de_change >=
> 0::double precision)
> « ckt_lignes_operation » CHECK (montant_total = (montant_operation +
> taxes_et_frais) AND montant_base = round((montant_total::double precision /
> taux_de_change)::numeric, 2))
> Contraintes de clés étrangères :
> « fk_lignes_operatio_carte_operation_cartes_bancaire » FOREIGN KEY
> (numero_carte) REFERENCES cartes_bancaires(numero_carte) ON UPDATE RESTRICT
> ON DELETE RESTRICT
> « fk_lignes_operatio_cheque_operation_cheques » FOREIGN KEY
> (che_code_banque, che_numero_guichet, che_numero_compte, numero_chequier,
> numero_cheque) REFERENCES cheques(code_banque, numero_guichet,
> numero_compte, numero_chequier, numero_cheque) ON UPDATE RESTRICT ON DELETE
> RESTRICT
> « fk_lignes_operatio_compte_ligne_operation_comptes » FOREIGN KEY
> (code_banque, numero_guichet, numero_compte) REFERENCES comptes(code_banque,
> numero_guichet, numero_compte) ON UPDATE RESTRICT ON DELETE RESTRICT
> « fk_lignes_operatio_devise_base_operation_devises » FOREIGN KEY
> (code_devise) REFERENCES devises(code_devise) ON UPDATE RESTRICT ON DELETE
> RESTRICT
> « fk_lignes_operatio_devise_operation_devises » FOREIGN KEY
> (devise_base) REFERENCES devises(code_devise) ON UPDATE RESTRICT ON DELETE
> RESTRICT
> « fk_lignes_operatio_document_operation_documents_lies » FOREIGN KEY
> (code_document) REFERENCES documents_lies(code_document) ON UPDATE RESTRICT
> ON DELETE RESTRICT
> « fk_lignes_operatio_lien_operation_echeance_cred_echeances_credi »
> FOREIGN KEY (code_credit, numero_echeance_credit) REFERENCES
> echeances_credit(code_credit, numero_echeance_credit) ON UPDATE RESTRICT ON
> DELETE RESTRICT
> « fk_lignes_operatio_ligne_operation_ecart2_ecarts_rapproch » FOREIGN
> KEY (code_ecart) REFERENCES ecarts_rapprochement(code_ecart) ON UPDATE
> RESTRICT ON DELETE RESTRICT
> « fk_lignes_operatio_moyen_operation_moyens_de_paiem » FOREIGN KEY
> (moy_code_banque, code_moyen, debit_credit) REFERENCES
> moyens_de_paiement(code_banque, code_moyen, debit_credit) ON UPDATE RESTRICT
> ON DELETE RESTRICT
> « fk_lignes_operatio_partenaire_operation_partenaires_ope » FOREIGN
> KEY (code_partenaire) REFERENCES partenaires_operation(code_partenaire) ON
> UPDATE RESTRICT ON DELETE RESTRICT
> « fk_lignes_operatio_releve_operation_releves_de_comp » FOREIGN KEY
> (rel_code_banque, rel_numero_guichet, rel_numero_compte, numero_releve)
> REFERENCES releves_de_compte(code_banque, numero_guichet, numero_compte,
> numero_releve) ON UPDATE RESTRICT ON DELETE RESTRICT
> « fk_lignes_operatio_statut_ligne_operation_statuts_operati » FOREIGN
> KEY (code_statut_operation) REFERENCES
> statuts_operation(code_statut_operation) ON UPDATE RESTRICT ON DELETE
> RESTRICT
> « fk_lignes_operatio_type_ligne_operation_types_operation » FOREIGN
> KEY (typ_code_banque, code_type_operation) REFERENCES
> types_operation(code_banque, code_type_operation) ON UPDATE RESTRICT ON
> DELETE RESTRICT
> « fk_lignes_operatio_utilisateur_operation_utilisateurs » FOREIGN KEY
> (code_utilisateur) REFERENCES utilisateurs(code_utilisateur) ON UPDATE
> RESTRICT ON DELETE RESTRICT
> « fk_lignes_operatio_virement_operation_lignes_operatio » FOREIGN KEY
> (lig_code_banque, lig_numero_guichet, lig_numero_compte,
> lig_numero_sequence) REFERENCES lignes_operation(code_banque,
> numero_guichet, numero_compte, numero_sequence) ON UPDATE RESTRICT ON DELETE
> RESTRICT
> Déclencheurs :
> tda_lignes_operation AFTER DELETE ON lignes_operation FOR EACH ROW
> EXECUTE PROCEDURE tda_lignes_operation_proc()
> tia_lignes_operation AFTER INSERT ON lignes_operation FOR EACH ROW
> EXECUTE PROCEDURE tia_lignes_operation_proc()
> tib_lignes_operation BEFORE INSERT ON lignes_operation FOR EACH ROW
> EXECUTE PROCEDURE tib_lignes_operation_proc()
> tua_lignes_operation AFTER UPDATE ON lignes_operation FOR EACH ROW
> EXECUTE PROCEDURE tua_lignes_operation_proc()
> tub_lignes_operation BEFORE UPDATE ON lignes_operation FOR EACH ROW
> EXECUTE PROCEDURE tub_lignes_operation_proc()
> z_notifies_lignes_operation AFTER INSERT OR DELETE OR UPDATE ON
> lignes_operation FOR EACH ROW EXECUTE PROCEDURE send_table_notifies()
> Tablespace « ezm_data »
>
> \d releves_de_compte
>
> Table « public.releves_de_compte »
> Colonne | Type | Modificateurs
> ----------------+-----------------------+---------------
> code_banque | character varying(9) | not null
> numero_guichet | character varying(22) | not null
> numero_compte | character varying(22) | not null
> numero_releve | character varying(10) | not null
> code_document | integer |
> date_arrete | date | not null
> Index :
> « pk_releves_de_compte » PRIMARY KEY, btree (code_banque,
> numero_guichet, numero_compte, numero_releve), tablespace « ezm_indexes »
> « document_releve_compte_fk_idx » btree (code_document), tablespace «
> ezm_indexes »
> « releves_compte_fk_idx » btree (code_banque, numero_guichet,
> numero_compte), tablespace « ezm_indexes »
> Contraintes de clés étrangères :
> « fk_releves_de_comp_document_releve_compte_documents_lies » FOREIGN
> KEY (code_document) REFERENCES documents_lies(code_document) ON UPDATE
> RESTRICT ON DELETE RESTRICT
> « fk_releves_de_comp_releves_compte_comptes » FOREIGN KEY
> (code_banque, numero_guichet, numero_compte) REFERENCES comptes(code_banque,
> numero_guichet, numero_compte) ON UPDATE RESTRICT ON DELETE RESTRICT
> Déclencheurs :
> tib_releves_de_compte BEFORE INSERT ON releves_de_compte FOR EACH ROW
> EXECUTE PROCEDURE tib_releves_de_compte_proc()
> Tablespace « ezm_data »
>
>
> explain select * from lignes_operation natural inner join releves_de_compte
> where numero_releve='2006-10';
>
> QUERY PLAN
>
>
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------------
> Nested Loop (cost=0.00..26.73 rows=1 width=551)
> Join Filter: (((lignes_operation.code_banque)::text =
> (releves_de_compte.code_banque)::text) AND
> ((lignes_operation.numero_guichet)::text =
> (releves_de_compte.numero_guichet)::text) AND
> ((lignes_operation.numero_compte)::text =
> (releves_de_compte.numero_compte)::text))
> -> Seq Scan on releves_de_compte (cost=0.00..10.15 rows=2 width=48)
> Filter: ('2006-10'::text = (numero_releve)::text)
> -> Index Scan using document_operation_fk_idx on lignes_operation
> (cost=0.00..8.27 rows=1 width=547)
> Index Cond: (lignes_operation.code_document =
> releves_de_compte.code_document)
> Filter: ((numero_releve)::text = '2006-10'::text)
> (7 lignes)
>
>
> explain select * from lignes_operation natural inner join releves_de_compte;
>
>
> QUERY PLAN
>
>
>
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> -------------------------------------------------
> Hash Join (cost=18.39..459.81 rows=1 width=551)
> Hash Cond: (((lignes_operation.code_banque)::text =
> (releves_de_compte.code_banque)::text) AND
> ((lignes_operation.numero_guichet)::text =
> (releves_de_compte.numero_guichet)::text) AND
> ((lignes_operation.numero_compte)::text =
> (releves_de_compte.numero_compte)::text) AND (lignes_operation.code_document
> = releves_de_compte.code_document) AND
> ((lignes_operation.numero_releve)::text =
> (releves_de_compte.numero_releve)::text))
> -> Seq Scan on lignes_operation (cost=0.00..301.83 rows=5583
> width=547)
> -> Hash (cost=9.12..9.12 rows=412 width=48)
> -> Seq Scan on releves_de_compte (cost=0.00..9.12 rows=412
> width=48)
> (5 lignes)
>
> version
>
> ----------------------------------------------------------------------------
> -------------------
> PostgreSQL 8.2.5 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
> (Ubuntu 4.1.2-0ubuntu4)
> (1 ligne)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com