1. Create a test table
CREATE TABLE big_data
(
id character varying(50) NOT NULL,
name character varying(50),
datetime timestamp with time zone,
CONSTRAINT big_data_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE big_data
OWNER TO postgres;< /p>
2, create insert data function
CREATE OR REPLACE FUNCTION insert_bigdata()
RETURNS text AS
$BODY$
declare ii integer;
declare jj integer;
begin
ii = 1;
jj = 1;
FOR ii IN 1..10 LOOP
FOR jj IN 1..10000 LOOP
INSERT INTO big_data values(uuid_generate_v4(),’lisi’||jj, now());
END LOOP;
END LOOP;
RETURN’success’;
end;
$BODY $
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_bigdata()
OWNER TO postgres;
3. Insert ten million pieces of data (modify the number of loops in the function , Execute it a few more times and insert the required data)
select insert_bigdata();
4. Count the execution time of the name field without indexing and adding indexes
< p>10 records of query results
select * from big_data where name=’lisi10′;
Check 100 records of query results
select * from big_data where name=’lisi100′;
1000 records of query results
select * from big_data where name=’lisi1000′;
< p>10000 records of query results
select * from big_data where name=’lisi10000′;
100000 records of query results
select * from big_data where name=’lisi100000′;
Time-consuming statistics table (unit/millisecond)
< /p>
Time-consuming statistics chart
5. Results summary
When the query result is less than 1000 records, adding an index will greatly improve the query efficiency.
When the query result is greater than 1000 records, the improvement of query efficiency by adding an index gradually decreases, especially when it exceeds 10,000, the query time after using the index is also relatively long.
The current result is only applicable to the created big_data data table (if there are more fields in the data table and the amount of data is relatively large, it will appear in a smaller number of query result records and index query efficiency will not improve significantly The problem). ———————————————— Copyright statement: This article is the original article of the CSDN blogger “Suoyu”, and it follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this for reprinting. statement. Original link: https://blog.csdn.net/shuoyu816/article/details/82793968