Skip site navigation (1) Skip section navigation (2)

Query performance problem in 8.0.0beta1

From: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query performance problem in 8.0.0beta1
Date: 2004-08-26 13:36:20
Message-ID: 008a01c48b71$abd89640$0501a8c0@comai04 (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, I have just installed 8.0.0beta1 and I noticed that some query are slower than 7.4.2 queries.

After a FULL VACUUM ANALYZE

***With 7.4.2***

explain analyze SELECT count(*) FROM "SNS_DATA" WHERE "Data_Arrivo_Campione" BETWEEN '2004-01-01 00:00:00' AND '2004-01-31 23:59:59' AND "Cod_Par" = '17476'

gives

 Aggregate  (cost=46817.89..46817.89 rows=1 width=0) (actual time=401.216..401.217 rows=1 loops=1)
   ->  Index Scan using snsdata_codpar on "SNS_DATA"  (cost=0.00..46817.22 rows=268 width=0) (actual time=165.948..400.258 rows=744 loops=1)
         Index Cond: (("Cod_Par")::text = '17476'::text)
         Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '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=93932.91..93932.91 rows=1 width=0) (actual time=14916.371..14916.371 rows=1 loops=1)
   ->  Seq Scan on "SNS_DATA"  (cost=0.00..93930.14 rows=1108 width=0) (actual time=6297.152..14915.330 rows=744 loops=1)
         Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '2004-01-31 23:59:59'::timestamp without time zone) AND (("Cod_Par")::text = '17476'::text))
 Total runtime: 14916.935 ms

And I if disable the seqscan
SET enable_seqscan = false;

I get the following Aggregate  (cost=158603.19..158603.19 rows=1 width=0) (actual time=4605.862..4605.863 rows=1 loops=1)
   ->  Index Scan using snsdata_codpar on "SNS_DATA"  (cost=0.00..158600.41 rows=1108 width=0) (actual time=2534.422..4604.865 rows=744 loops=1)
         Index Cond: (("Cod_Par")::text = '17476'::text)
         Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '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 
shared_buffer = 2048
work_mem = sort_mem = 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 EXECUTE PROCEDURE sns_action()


Can it be a datatype conversion problem?
Thanks in advance!
Reds




Responses

pgsql-performance by date

Next:From: Tore HalsetDate: 2004-08-26 15:13:24
Subject: Re: Anyone familiar with Apple Xserve RAID
Previous:From: Andrew RawnsleyDate: 2004-08-26 12:07:42
Subject: Re: Anyone familiar with Apple Xserve RAID

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group