Re: Performance problem with query

On 19/07/2006, at 10:03 PM, Christian Rengstl wrote:

> So here's the master table including the rules:
>
> entry_no int8 NOT NULL DEFAULT nextval('public.master_seq'::regclass),
>   pid varchar(15) NOT NULL,
>   val_1 varchar(1),
>   val_2 varchar(1),
>   chr int2 NOT NULL,
>   aendat timestamp DEFAULT now(),
>   aennam varchar(8),
>   CONSTRAINT "PK_ENTRY" PRIMARY KEY (entry_no),
>   CONSTRAINT "UNIQUE_MASTER" UNIQUE (pid, entry_no)
>
> CREATE OR REPLACE RULE "INSERT_INTO_1" AS
>     ON INSERT TO public.master
>    WHERE new.chr = 1 DO INSTEAD  INSERT INTO public.table_1
> (entry_no, pid, val_1, val_2, chr, aendat, aennam)
>   VALUES (new.entry_no, new.pid, new.val_1, new.val_2, new.chr,
> new.aendat, new.aennam);
>
> Like this i have around 20 rules so far, but there might be more
> later on. The children tables are so far exactly as the master table.

What about the children? Do they have the same indexes?

You could try adding an 'ORDER BY chr' to your long running INSERT
INTO ... SELECT ... query.

>>> Obviously it had something to do with the rule, because now
>>> everything finished within 20 minutes. the problem is just that i
>>> don't really want to give up the inheritance design. is there a way
>>> to maintain the inheritance that doesn't cause this huge
>>> performance problem?

When you say "now everything finished within 20 minutes", what did
you actually do to achieve this?


--
Seeya...Q

                -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

                           _____  /  Quinton Dolan - qdolan@gmail.com
   __  __/  /   /   __/   /      /
      /    __  /   _/    /      /        Gold Coast, QLD, Australia
   __/  __/ __/ ____/   /   -  /            Ph: +61 419 729 806
                     _______  /
                             _\