Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-08 Thread Enrico Weigelt
* David Gagnon [EMAIL PROTECTED] wrote: FOR inventoryTransaction IN SELECT DISTINCT IRNUM, IRAENUM, IRSTATUT, IRSENS, IRSOURCE, IRDATE, IRQTE FROM IR WHERE IRNUM = ANY (requestIds) and IRYPNUM = companyId LOOP hmm. you probably could create the query dynamically

Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-05 Thread Bruno Wolff III
On Mon, Jul 04, 2005 at 20:29:50 -0400, David Gagnon [EMAIL PROTECTED] wrote: Thanks .. I miss that FK don't create indexed ... since Primary key implicitly does ... I'm a bit surprised of that behavior thought, since it means that if we delete a row from table A all tables (B,C,D) with

Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-05 Thread Stephan Szabo
On Mon, 4 Jul 2005, David Gagnon wrote: Thanks .. I miss that FK don't create indexed ... since Primary key implicitly does ... I'm a bit surprised of that behavior thought, since it means that if we delete a row from table A all tables (B,C,D) with FK pointing to this table (A) must be

Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-05 Thread Tom Lane
David Gagnon [EMAIL PROTECTED] writes: explain analyse SELECT IRNUM FROM IR INNER JOIN IT ON IT.ITIRNUM = ANY ('{1000, 2000}') AND IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM = IT.ITIRNUM WHERE IRNUM = ANY ('{1000, 2000}') and IRYPNUM = 'M' Those =ANY

[PERFORM] Why the planner is not using the INDEX .

2005-07-04 Thread David Gagnon
Hi all, If you can just help my understanding the choice of the planner. Here is the Query: explain analyse SELECT IRNUM FROM IR INNER JOIN IT ON IT.ITIRNUM = ANY ('{1000, 2000}') AND IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM = IT.ITIRNUM WHERE IRNUM = ANY ('{1000,

Re: [PERFORM] Why the planner is not using the INDEX .

2005-07-04 Thread Christopher Kings-Lynne
I'm a bit surprised of that behavior thought, since it means that if we delete a row from table A all tables (B,C,D) with FK pointing to this table (A) must be scanned. If there is no index on those tables it means we gone do all Sequantial scans. Than can cause significant performance