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