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
Re: [PERFORM] Why the planner is not using the INDEX .
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.
Re: [PERFORM] plain inserts and deletes very slow
On Mon, 4 Jul 2005 10:59:03 +0200, Enrico Weigelt <[EMAIL PROTECTED]> wrote: > * David Mitchell <[EMAIL PROTECTED]> wrote: > > Hi, > > > Hmm, you said you don't experience this when executing the query > > manually. What adapter are you using to access postgres from your > > application? libpq, npgsql or something else? > > huh, its a delphi application ... (I didnt code it). Turn on statement logging. I've seen delphi interfaces do extra queries on system tables to find some structure information. The available interfaces for delphi that I know of are vitavoom's dbexpress (you should be able to find dbexppge.dll), zeos (you'll have to grep the executable), ODBC using ADO or bde, Or dot net. klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Why the planner is not using the INDEX .
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.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[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] plain inserts and deletes very slow
On Mon, Jul 04, 2005 at 10:57:29AM +0200, Enrico Weigelt wrote: > * Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > On Mon, Jul 04, 2005 at 02:17:47AM +0200, Enrico Weigelt wrote: > > > * David Mitchell <[EMAIL PROTECTED]> wrote: > > > > Perhaps if you are doing a lot of inserts and deletes, vacuuming every > > > > 6 > > > > minutes would be closer to your mark. Try vacuuming every 15 minutes > > > > for > > > > a start and see how that affects things (you will have to do a vacuum > > > > full to get the tables back into shape after them slowing down as they > > > > have). > > > > > > hmm. I've just done vacuum full at the moment on these tables, but it > > > doesnt seem to change anything :( > > > > Maybe you need a REINDEX, if you have indexes on that table. Try that, > > coupled with the frequent VACUUM suggestion. > > I've tried it, but it doesn't seem to help :( So, lets back up a little. You have no table nor index bloat, because you reindexed and full-vacuumed. So where does the slowness come from? Can you post an example EXPLAIN ANALYZE of the queries in question? -- Alvaro Herrera () "El realista sabe lo que quiere; el idealista quiere lo que sabe" (Anónimo) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
* Alvaro Herrera <[EMAIL PROTECTED]> wrote: > On Mon, Jul 04, 2005 at 02:17:47AM +0200, Enrico Weigelt wrote: > > * David Mitchell <[EMAIL PROTECTED]> wrote: > > > Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 > > > minutes would be closer to your mark. Try vacuuming every 15 minutes for > > > a start and see how that affects things (you will have to do a vacuum > > > full to get the tables back into shape after them slowing down as they > > > have). > > > > hmm. I've just done vacuum full at the moment on these tables, but it > > doesnt seem to change anything :( > > Maybe you need a REINDEX, if you have indexes on that table. Try that, > coupled with the frequent VACUUM suggestion. I've tried it, but it doesn't seem to help :( 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 8: explain analyze is your friend
Re: [PERFORM] plain inserts and deletes very slow
* David Mitchell <[EMAIL PROTECTED]> wrote: Hi, > Hmm, you said you don't experience this when executing the query > manually. What adapter are you using to access postgres from your > application? libpq, npgsql or something else? huh, its a delphi application ... (I didnt code it). > And what is your method for running the query 'manually'. Are you > running it locally or from a remote machine or what? using psql remotely - database and client machines are sitting on the same wire. 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 4: Don't 'kill -9' the postmaster
