X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 473C65E3639 for ; Fri, 27 Aug 2004 03:54:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75409-08 for ; Fri, 27 Aug 2004 06:54:18 +0000 (GMT) Received: from smtp-bal.opennet.it (noder32-48.opennet.it [212.110.32.48]) by svr1.postgresql.org (Postfix) with SMTP id BA5C45E46C2 for ; Fri, 27 Aug 2004 03:54:15 -0300 (ADT) Received: (qmail 16695 invoked from network); 27 Aug 2004 06:53:23 -0000 Received: from unknown (HELO comai04) (212.110.56.34) by 0 with SMTP; 27 Aug 2004 06:53:23 -0000 Message-ID: <007c01c48c03$269b50c0$0501a8c0@comai04> From: "Stefano Bonnin" To: Subject: Query performance issue with 8.0.0beta1 Date: Fri, 27 Aug 2004 08:57:43 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01C48C13.E9F01870" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/385 X-Sequence-Number: 8021 This is a multi-part message in MIME format. ------=_NextPart_000_0079_01C48C13.E9F01870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have just installed 8.0.0beta1 and I noticed that some query are slow= er than 7.4.2 queries. After: pg_dump my_database >mydb.sql (from 7.4.2) psql my_new_database Index Scan using snsdata_codpar on "SNS_DATA" (cost=3D0.00..46817.2= 2 rows=3D268 width=3D0) (actual time=3D165.948..400.258 rows=3D744 loops=3D= 1) Index Cond: (("Cod_Par")::text =3D '17476'::text) Filter: (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::times= tamp without time zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:= 59'::timestamp without time zone)) Total runtime: 401.302 ms ***while on 8.0.0*** the same query gives ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------------- Aggregate (cost=3D93932.91..93932.91 rows=3D1 width=3D0) (actual time=3D1= 4916.371..14916.371 rows=3D1 loops=3D1) -> Seq Scan on "SNS_DATA" (cost=3D0.00..93930.14 rows=3D1108 width=3D0= ) (actual time=3D6297.152..14915.330 rows=3D744 loops=3D1) Filter: (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::times= tamp without time zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:= 59'::timestamp without time zone) AND (("Cod_Par")::text =3D '17476'::text)) Total runtime: 14916.935 ms And I if disable the seqscan SET enable_seqscan =3D false; I get the following: Aggregate (cost=3D158603.19..158603.19 rows=3D1 width=3D0) (actual time=3D= 4605.862..4605.863 rows=3D1 loops=3D1) -> Index Scan using snsdata_codpar on "SNS_DATA" (cost=3D0.00..158600.= 41 rows=3D1108 width=3D0) (actual time=3D2534.422..4604.865 rows=3D744 loop= s=3D1) Index Cond: (("Cod_Par")::text =3D '17476'::text) Filter: (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::times= tamp without time zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:= 59'::timestamp without time zone)) Total runtime: 4605.965 ms The total runtime is bigger (x10 !!) than the old one. The memory runtime parameters are=20 shared_buffer =3D 2048 work_mem =3D sort_mem =3D 2048 SNS_DATA shema is the following: Table "public.SNS_DATA" Column | Type | Modifiers ----------------------+-----------------------------+-------------------- Ordine | integer | not null default 0 Cod_Par | character varying(100) | not null Cod_Ana | character varying(100) | not null Valore | character varying(255) | Descriz | character varying(512) | Un_Mis | character varying(70) | hash | integer | valid | boolean | default true alarm | boolean | default false Cod_Luogo | character varying(30) | Data_Arrivo_Campione | timestamp without time zone | site_id | integer | Cod_Luogo_v | character varying(30) | repeated_val | boolean | default false Indexes: "sns_data2_pkey" PRIMARY KEY, btree ("Ordine", "Cod_Ana", "Cod_Par") "sns_datacodluogo2" btree ("Cod_Luogo") "sns_datatimefield2" btree ("Data_Arrivo_Campione") "sns_siteid2" btree (site_id) "sns_valid2" btree ("valid") "snsdata_codana" btree ("Cod_Ana") "snsdata_codpar" btree ("Cod_Par") Foreign-key constraints: "$2" FOREIGN KEY ("Cod_Ana") REFERENCES "SNS_ANA"("Cod_Ana") ON DELETE = CASCADE Triggers: sns_action_tr BEFORE INSERT OR UPDATE ON "SNS_DATA" FOR EACH ROW EXECUT= E PROCEDURE sns_action() The table has 2M of records Can it be a datatype conversion issue? Can it be depend on the the type of restore (with COPY commands)? I have no idea. Thanks in advance! Reds ------=_NextPart_000_0079_01C48C13.E9F01870 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, I have just installed 8.0.0beta1 and I= noticed=20 that some query are slower than 7.4.2 queries.
 
After:
pg_dump my_database >mydb.sql (from=20 7.4.2)
psql my_new_database <mydb.sql (to 8.0.0 with COPY instead of=20 INSERT)
FULL VACUUM ANALYZE
 
***With the old db on 7.4.2***
 
explain analyze SELECT count(*) FROM "SNS_= DATA"=20 WHERE "Data_Arrivo_Campione" BETWEEN '2004-01-01 00:00:00' AND '2004-01-31= =20 23:59:59' AND "Cod_Par" =3D '17476'
 
gives
 
 Aggregate  (cost=3D46817.89..46= 817.89=20 rows=3D1 width=3D0) (actual time=3D401.216..401.217 rows=3D1 loops=3D1)
=   =20 ->  Index Scan using snsdata_codpar on "SNS_DATA" =20 (cost=3D0.00..46817.22 rows=3D268 width=3D0) (actual time=3D165.948..400.25= 8 rows=3D744=20 loops=3D1)
         Index Cond:= =20 (("Cod_Par")::text =3D=20 '17476'::text)
         Filter:= =20 (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::timestamp without t= ime=20 zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:59'::timestamp = without=20 time zone))
 Total runtime: 401.302 ms
 
***while on 8.0.0***
 
the same query gives
 
------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----
 Aggregate =20 (cost=3D93932.91..93932.91 rows=3D1 width=3D0) (actual time=3D14916.371..14= 916.371=20 rows=3D1 loops=3D1)
   ->  Seq Scan on "SNS_DATA" = ;=20 (cost=3D0.00..93930.14 rows=3D1108 width=3D0) (actual time=3D6297.152..1491= 5.330=20 rows=3D744 loops=3D1)
         F= ilter:=20 (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::timestamp without t= ime=20 zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:59'::timestamp = without=20 time zone) AND (("Cod_Par")::text =3D '17476'::text))
 Total runtim= e:=20 14916.935 ms
And I if disable the seqscan
SET enable_seqscan =3D false;
 
I get the following:
 
Aggregate  (cost=3D158603.19..158603.= 19 rows=3D1=20 width=3D0) (actual time=3D4605.862..4605.863 rows=3D1 loops=3D1)
 &= nbsp;=20 ->  Index Scan using snsdata_codpar on "SNS_DATA" =20 (cost=3D0.00..158600.41 rows=3D1108 width=3D0) (actual time=3D2534.422..460= 4.865=20 rows=3D744 loops=3D1)
         I= ndex=20 Cond: (("Cod_Par")::text =3D=20 '17476'::text)
         Filter:= =20 (("Data_Arrivo_Campione" >=3D '2004-01-01 00:00:00'::timestamp without t= ime=20 zone) AND ("Data_Arrivo_Campione" <=3D '2004-01-31 23:59:59'::timestamp = without=20 time zone))
 Total runtime: 4605.965 ms
The total runtime is bigger (x10 !!) than = the old=20 one.
 
The memory runtime parameters are <= /DIV>
shared_buffer =3D 2048
work_mem =3D sort_mem =3D 2048
 
SNS_DATA shema is the following:
 
           =             &nb= sp;=20 Table "public.SNS_DATA"
       =20 Column       =20 |           =20 Type            = ;=20 |    =20 Modifiers
----------------------+-----------------------------+---------= -----------
 Ordine        =       =20 |=20 integer           &n= bsp;        =20 | not null default=20 0
 Cod_Par         &nb= sp;   =20 | character varying(100)      | not=20 null
 Cod_Ana         =     =20 | character varying(100)      | not=20 null
 Valore         &= nbsp;    =20 | character varying(255)     =20 |
 Descriz         &nb= sp;   =20 | character varying(512)     =20 |
 Un_Mis         &nbs= p;    =20 | character varying(70)      =20 |
 hash          =       =20 |=20 integer           &n= bsp;        =20 |
 valid          = ;     =20 |=20 boolean           &n= bsp;        =20 | default=20 true
 alarm         &n= bsp;     =20 |=20 boolean           &n= bsp;        =20 | default=20 false
 Cod_Luogo        &nb= sp;  =20 | character varying(30)      =20 |
 Data_Arrivo_Campione | timestamp without time zone=20 |
 site_id         &nb= sp;   =20 |=20 integer           &n= bsp;        =20 |
 Cod_Luogo_v         = ; |=20 character varying(30)      =20 |
 repeated_val         |= =20 boolean           &n= bsp;        =20 | default false
Indexes:
    "sns_data2_pkey" PRIMARY = KEY,=20 btree ("Ordine", "Cod_Ana", "Cod_Par")
    "sns_datacodlu= ogo2"=20 btree ("Cod_Luogo")
    "sns_datatimefield2" btree=20 ("Data_Arrivo_Campione")
    "sns_siteid2" btree=20 (site_id)
    "sns_valid2" btree=20 ("valid")
    "snsdata_codana" btree=20 ("Cod_Ana")
    "snsdata_codpar" btree=20 ("Cod_Par")
Foreign-key constraints:
    "$2" FOREIGN = KEY=20 ("Cod_Ana") REFERENCES "SNS_ANA"("Cod_Ana") ON DELETE=20 CASCADE
Triggers:
    sns_action_tr BEFORE INSERT OR U= PDATE=20 ON "SNS_DATA" FOR EACH ROW EXECUTE PROCEDURE sns_action()
 
The table has 2M of records
Can it be a datatype conversion issue?
Can it be depend on the the type of restore (with COPY commands)?
I have no idea.
 
Thanks in advance!
Reds
 
------=_NextPart_000_0079_01C48C13.E9F01870--