pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stefano Bonnin <stefano.bonnin@comai.to>
To: pgsql-performance@postgresql.org
Subject: Query performance problem in 8.0.0beta1
Date: Thu, 26 Aug 2004 15:36:20 +0200
Message-ID: <008a01c48b71$abd89640$0501a8c0@comai04> (raw)

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

view thread (2+ messages)  latest in thread

Message-ID: <008a01c48b71$abd89640$0501a8c0@comai04>
Permalink:  ../008a01c48b71$abd89640$0501a8c0@comai04/
Also on:    postgresql.org/message-id/008a01c48b71$abd89640$0501a8c0@comai04

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: stefano.bonnin@comai.to
  Subject: Re: Query performance problem in 8.0.0beta1
  In-Reply-To: <008a01c48b71$abd89640$0501a8c0@comai04>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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