Re: [PERFORM] Why the planner is not using the INDEX .
* 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 and then execute it. BTW: why isn't IN not usable with arrays ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Why the planner is not using the INDEX .
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 FK pointing to this table (A) must be scanned. But in some applications you don't ever do that, so you don't save anything by having the index for deletes but have to pay the cost to update it when modifying the referencing table. If you think an index will help in your case, just create one. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Why the planner is not using the INDEX .
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 scanned. If there is no index on those tables it means we gone do all Sequantial scans. Than can cause significant performance problem!!!. Is there a reason why implicit index aren't created when FK are declared. I looked into the documentation and I haven't found a way to The reason is that it's not always useful to have an index for that purpose. You could either have low selectivity (in which case the index wouldn't be used) or low/batch changes to the referenced table (in which case the cost of maintaining the index may be greater than the value of having the index) or other such cases. In primary key and unique, we currently have no choice but to make an index because that's how the constraint is currently implemented. tell postgresql to automatically create an index when creating la FK. Does it means I need to manage it EXPLICITLY with create index statement ? Yeah. Is there another way ? Not that I can think of without changing the source. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why the planner is not using the INDEX .
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 constructs are not currently optimizable at all. You might
get better results with IT.ITIRNUM IN (1000, 2000) etc.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[PERFORM] Why the planner is not using the INDEX .
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, 2000}') and IRYPNUM = 'M'
Here is the Query plan:
QUERY PLAN
Hash Join (cost=1142.47..5581.75 rows=87 width=4) (actual
time=125.000..203.000 rows=2 loops=1)
Hash Cond: ("outer".itirnum = "inner".irnum)
- Seq Scan on it
(cost=0.00..3093.45 rows=31646 width=9) (actual
time=0.000..78.000 rows=2 loops=1)
Filter: ((itirnum = ANY ('{1000,2000}'::integer[])) AND
((itypnum)::text = 'M'::text))
- Hash (cost=1142.09..1142.09
rows=151 width=37) (actual time=125.000..125.000 rows=0 loops=1)
- Index Scan using ir_pk on ir (cost=0.00..1142.09 rows=151 width=37) (actual
time=0.000..125.000 rows=2 loops=1)
Index Cond: ((irypnum)::text = 'M'::text)
Filter: (irnum = ANY ('{1000,2000}'::integer[]))
Total
runtime: 203.000 ms
I don't understand why the planner do a Seq Scan (Seq Scan on table
IT ..) instead of passing by the followin index:
ALTER TABLE IT ADD CONSTRAINT IT_IR_FK foreign key
(ITYPNUM,ITIRNUM) references IR (IRYPNUM, IRNUM) ON UPDATE CASCADE;
I tried some stuff but I'm not able to change this behavior. The IT
and IR table may be quite huge (from 20k to 1600k rows) so I think
doing a SEQ SCAN is not a good idea.. am I wrong? Is this query plan
is oki for you ?
Thanks for your help.
/David
P.S.: I'm using postgresql 8.0.3 on windows and I change those setting
in my postgresql.conf :
shared_buffers = 12000 # min 16, at least max_connections*2, 8KB
each
work_mem = 15000 # min 64, size in KB
Re: [PERFORM] Why the planner is not using the INDEX .
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 problem!!!. Correct. Is there a reason why implicit index aren't created when FK are declared. Because it's not a requirement... I looked into the documentation and I haven't found a way to tell postgresql to automatically create an index when creating la FK. Does it means I need to manage it EXPLICITLY with create index statement ? Is there another way ? No other way - you need to explicitly create them. It's not that hard either to write a query to search the system catalogs for unindexed FK's. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
