From: 常超 <chang-chao@hotmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: how do functions affect query plan?
Date: Thu, 15 May 2014 14:16:47 +0900
Message-ID: <BAY174-W47F6408BADFE4011010B7F84360@phx.gbl> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,all
I have a table to save received measure data.
CREATE TABLE measure_data
(
id serial NOT NULL,
telegram_id integer NOT NULL,
measure_time timestamp without time zone NOT NULL,
item_id integer NOT NULL,
val double precision,
CONSTRAINT measure_data_pkey PRIMARY KEY (id)
);
CREATE INDEX index_measure_data_telegram_id ON measure_data USING btree (telegram_id);
in my scenario,a telegram contains measure data for multiple data items and timestamps,
BTW,another table is for telegram.
The SQL I used in my application is
select * from measure_data where telegram_id in(1,2,...,n)
and this query used the index_measure_data_telegram_id index,as expected.
In order to see the performance of my query ,
I used the following query to search the measure data for randomly 30 telegrams.
explain analyze
SELECT md.*
FROM measure_data md
where telegram_id in
(
SELECT distinct
trunc((132363-66484) * random() + 66484)
FROM generate_series(1,30) as s(telegram_id)
)
;
the 132363 and 66484 are the max and min of the telegram id,separately.
What surprised me is that index is not used,instead,a seq scan is performed on measure_data.
Although,intuitively,in this case,it is much wiser to use the index.
Would you please give some clue to why this happened?
"Hash Semi Join (cost=65.00..539169.32 rows=10277280 width=28) (actual time=76.454..17177.054 rows=9360 loops=1)"
" Hash Cond: ((md.telegram_id)::double precision = (trunc(((65879::double precision * random()) + 66484::double precision))))"
" -> Seq Scan on measure_data md (cost=0.00..356682.60 rows=20554560 width=28) (actual time=0.012..13874.809 rows=20554560 loops=1)"
" -> Hash (cost=52.50..52.50 rows=1000 width=8) (actual time=0.062..0.062 rows=30 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 2kB"
" -> HashAggregate (cost=22.50..42.50 rows=1000 width=0) (actual time=0.048..0.053 rows=30 loops=1)"
" -> Function Scan on generate_series s (cost=0.00..20.00 rows=1000 width=0) (actual time=0.020..0.034 rows=30 loops=1)"
"Total runtime: 17177.527 ms"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: chang-chao@hotmail.com
Subject: Re: how do functions affect query plan?
In-Reply-To: <BAY174-W47F6408BADFE4011010B7F84360@phx.gbl>
* 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