Sometimes, use PREPARED STATEMENT or Function, maybe slower then just run SQL . Why?
This from explain analyze plan.
Test a table as below:
CREATE TABLE tbl_user
(
id integer NOT NULL,
firstname character varying(30),
lastname character varying(30),
corp character varying(20),
age integer,
CONSTRAINT tbl_user_pk PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
;
ALTER TABLE tbl_user OWNER TO postgres;
GRANT ALL ON TABLE tbl_user TO public;
CREATE INDEX idx_user_age ON tbl_user USING btree (age);
bmcv3=# \d tbl_user
Table "public.tbl_user"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
firstname | character varying(30) |
lastname | character varying(30) |
corp | character varying(20) |
age | integer |
Indexes:
"tbl_user_pk" PRIMARY KEY, btree (id)
"idx_user_age" btree (age)
bmcv3=# insert into tbl_user select generate_series(1,100000),'huang','kyle','bsmart',27;
INSERT 0 100000
bmcv3=# insert into tbl_user select generate_series(100001,100100),'huang','kyle','bsmart',generate_series(1,100);
INSERT 0 100
bmcv3=# analyze tbl_user;
ANALYZE
bmcv3=# select age,count(*) from tbl_user group by age order by count(*);
age | count
-----+--------
68 | 1
8 | 1
11 | 1
80 | 1
16 | 1
39 | 1
54 | 1
3 | 1
47 | 1
61 | 1
96 | 1
67 | 1
87 | 1
14 | 1
46 | 1
99 | 1
48 | 1
17 | 1
28 | 1
83 | 1
36 | 1
94 | 1
15 | 1
84 | 1
88 | 1
66 | 1
77 | 1
38 | 1
4 | 1
30 | 1
89 | 1
60 | 1
50 | 1
74 | 1
33 | 1
73 | 1
95 | 1
6 | 1
40 | 1
56 | 1
53 | 1
62 | 1
71 | 1
19 | 1
29 | 1
93 | 1
2 | 1
21 | 1
57 | 1
51 | 1
72 | 1
92 | 1
97 | 1
23 | 1
41 | 1
31 | 1
35 | 1
65 | 1
75 | 1
52 | 1
76 | 1
20 | 1
69 | 1
5 | 1
44 | 1
7 | 1
37 | 1
85 | 1
34 | 1
82 | 1
81 | 1
25 | 1
32 | 1
12 | 1
58 | 1
1 | 1
10 | 1
79 | 1
26 | 1
42 | 1
90 | 1
18 | 1
59 | 1
78 | 1
98 | 1
100 | 1
86 | 1
13 | 1
49 | 1
22 | 1
63 | 1
9 | 1
24 | 1
91 | 1
64 | 1
70 | 1
45 | 1
43 | 1
55 | 1
27 | 100001
(100 rows)
#if where condition use AGE, mayby full table scan (age=27), maybe use index (age =1)
bmcv3=# explain analyze select * from tbl_user where age=27;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on tbl_user (cost=0.00..51524.00 rows=100007 width=26) (actual time=0.013..23.144 rows=100001 loops=1)
Filter: (age = 27)
Total runtime: 29.563 ms
(3 rows)
bmcv3=# explain analyze select * from tbl_user where age=1;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..30.53 rows=3 width=26) (actual time=0.023..0.025 rows=1 loops=1)
Index Cond: (age = 1)
Total runtime: 0.056 ms
(3 rows)
# Try prepared statement
bmcv3=# prepare p_user (int) as select * from tbl_user where age=$1;
PREPARE
bmcv3=# explain analyze execute p_user(27);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..1847.67 rows=3452 width=26) (actual time=0.035..32.628 rows=100001 loops=1)
Index Cond: (age = $1)
Total runtime: 39.357 ms
(3 rows)
bmcv3=# explain analyze execute p_user(1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..1847.67 rows=3452 width=26) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: (age = $1)
Total runtime: 0.065 ms
(3 rows)
# about 100,000 records , about 10ms
# how about 10,000,000 records
bmcv3=# insert into tbl_user select generate_series(100101,9999999),'huang','kyle','bsmart',27;
INSERT 0 9899899
bmcv3=# explain analyze execute p_user(27);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..2633340.75 rows=5000040 width=26) (actual time=0.054..3683.657 rows=9999900 loops=1)
Index Cond: (age = $1)
Total runtime: 4350.579 ms
(3 rows)
bmcv3=# explain analyze execute p_user(1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..2633340.75 rows=5000040 width=26) (actual time=0.027..0.028 rows=1 loops=1)
Index Cond: (age = $1)
Total runtime: 0.068 ms
(3 rows)
bmcv3=# explain analyze select * from tbl_user where age=27;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_user (cost=0.00..5147100.00 rows=9999747 width=26) (actual time=0.027..2186.475 rows=9999900 loops=1)
Filter: (age = 27)
Total runtime: 2815.308 ms
(3 rows)
bmcv3=# explain analyze select * from tbl_user where age=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_user_age on tbl_user (cost=0.00..204.12 rows=333 width=26) (actual time=0.024..0.026 rows=1 loops=1)
Index Cond: (age = 1)
Total runtime: 0.056 ms
(3 rows)
diff 1535ms
But this article ask you not to use prepared statement.
Thanks Digoal
阅读(766) | 评论(0) | 转发(0) |
