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 |