pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery performance problem in 8.0.0beta1
2+ messages / 2 participants
[nested] [flat]
* Query performance problem in 8.0.0beta1
@ 2004-08-26 13:36 Stefano Bonnin <stefano.bonnin@comai.to>
0 siblings, 1 reply; 2+ messages in thread
From: Stefano Bonnin @ 2004-08-26 13:36 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query performance problem in 8.0.0beta1
@ 2004-08-29 22:31 Russell Smith <mr-russ@pws.com.au>
parent: Stefano Bonnin <stefano.bonnin@comai.to>
0 siblings, 0 replies; 2+ messages in thread
From: Russell Smith @ 2004-08-29 22:31 UTC (permalink / raw)
To: pgsql-performance
Hi,
I assume you have reposted because you have just signed up to the list.
If this is the case, can you please read the archives or replies to your original post about this question.
It did make it onto the archives and myself and others did reply with a few ideas and questions.
If you could address those in a reply mail that would help everybody with your problem
Regards
Russell Smith
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-08-29 22:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 13:36 Query performance problem in 8.0.0beta1 Stefano Bonnin <stefano.bonnin@comai.to>
2004-08-29 22:31 ` Russell Smith <mr-russ@pws.com.au>
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