pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy the planner is not using the INDEX .
9+ messages / 6 participants
[nested] [flat]
* Why the planner is not using the INDEX .
@ 2005-07-04 19:57 David Gagnon <dgagnon@siunik.com>
0 siblings, 2 replies; 9+ messages in thread
From: David Gagnon @ 2005-07-04 19:57 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-04 20:27 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: David Gagnon <dgagnon@siunik.com>
1 sibling, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2005-07-04 20:27 UTC (permalink / raw)
To: David Gagnon <dgagnon@siunik.com>; +Cc: pgsql-performance
On Mon, 4 Jul 2005, David Gagnon wrote:
> 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;
That doesn't create an index on IT. Primary keys (and unique constraints)
create indexes, but not foreign keys. Did you also create an index on
those fields?
Also it looks like it's way overestimating the number of rows that
condition would succeed for. You might consider raising the statistics
targets on those columns and reanalyzing.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 00:29 David Gagnon <dgagnon@siunik.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 3 replies; 9+ messages in thread
From: David Gagnon @ 2005-07-05 00:29 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance
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
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 ?
Thanks for you help that simple answer will solve a lot of performance
problem I have ...
/David
>On Mon, 4 Jul 2005, David Gagnon wrote:
>
>
>
>> 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;
>>
>>
>
>That doesn't create an index on IT. Primary keys (and unique constraints)
>create indexes, but not foreign keys. Did you also create an index on
>those fields?
>
>Also it looks like it's way overestimating the number of rows that
>condition would succeed for. You might consider raising the statistics
>targets on those columns and reanalyzing.
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 01:13 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: David Gagnon <dgagnon@siunik.com>
2 siblings, 0 replies; 9+ messages in thread
From: Christopher Kings-Lynne @ 2005-07-05 01:13 UTC (permalink / raw)
To: David Gagnon <dgagnon@siunik.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 12:32 Bruno Wolff III <bruno@wolff.to>
parent: David Gagnon <dgagnon@siunik.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2005-07-05 12:32 UTC (permalink / raw)
To: David Gagnon <dgagnon@siunik.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance
On Mon, Jul 04, 2005 at 20:29:50 -0400,
David Gagnon <dgagnon@siunik.com> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 14:02 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: David Gagnon <dgagnon@siunik.com>
2 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2005-07-05 14:02 UTC (permalink / raw)
To: David Gagnon <dgagnon@siunik.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 16:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Gagnon <dgagnon@siunik.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2005-07-05 16:09 UTC (permalink / raw)
To: David Gagnon <dgagnon@siunik.com>; +Cc: pgsql-performance
David Gagnon <dgagnon@siunik.com> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-05 17:53 David Gagnon <dgagnon@siunik.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: David Gagnon @ 2005-07-05 17:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>David Gagnon <dgagnon@siunik.com> 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
>
>
>
I already tried this construct. But the statement comes from a stored
procedure where the {1000, 2000} is an array variable (requestIds). I
tried to use
IT.ITIRNUM IN (requestIds) or several other variant without success.
Is there a way to make it work? Here is the statement the statement from the store procedure. Remenber requestIds is an array of int.
FOR inventoryTransaction IN
SELECT DISTINCT IRNUM, IRAENUM, IRSTATUT, IRSENS, IRSOURCE,
IRDATE, IRQTE
FROM IR
WHERE IRNUM = ANY (requestIds) and IRYPNUM = companyId
LOOP
Thank for your help !!!!
/David
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why the planner is not using the INDEX .
@ 2005-07-08 15:36 Enrico Weigelt <weigelt@metux.de>
parent: David Gagnon <dgagnon@siunik.com>
0 siblings, 0 replies; 9+ messages in thread
From: Enrico Weigelt @ 2005-07-08 15:36 UTC (permalink / raw)
To: pgsql-performance
* David Gagnon <dgagnon@siunik.com> 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: contact@metux.de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-07-08 15:36 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-04 19:57 Why the planner is not using the INDEX . David Gagnon <dgagnon@siunik.com>
2005-07-04 20:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-07-05 00:29 ` David Gagnon <dgagnon@siunik.com>
2005-07-05 01:13 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-07-05 12:32 ` Bruno Wolff III <bruno@wolff.to>
2005-07-05 14:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-07-05 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-05 17:53 ` David Gagnon <dgagnon@siunik.com>
2005-07-08 15:36 ` Enrico Weigelt <weigelt@metux.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox