1)Analyze:
syntax: analyze <table-name>
==>Which will updates statistics (system_tables) (or) That autovaccum will update after configured time,Then only we can see updated statistics.
2)Analyze verbose:
syntax:analyze verbose <table-name>
==> shows number of pages and Number of live records(usually deleted rows wont get cleaned up immediately once we call Analyze then only dead rows will cleaned up).
3)vacuum:
Sysntax: Vacuum <table-name>
This will removes dead rows. Main use of vacuum is delete dead row which will increase performance.
How to view hidden(deleted) fields of all tables.
Ex: select c+id,* from emp;
output:
c+id id
(0,1) 1
(0,2) 2
-----
(0,255) 255
(1,1) 256
Note: If we remove dead rows from table we can achieve "good performance".
4)Vacuum full:
vacuum full will gives returns disk space to disk.
Note: In PostgerSQL each row will have trasaction Id:
How to view transcation id of each row of table?
Answer: select xmin,c+id,* from emp;
5)Vacuum freeze:
Under one transaction we can inserts 4 milion.If we insert above 4 milions in this case 4 milion are inserted then very first one will replaced with last row(4 milion +1).
To over come above issue vacuum freeze will be run by autovacuum for every 2 million records.
6)Vacuum analyze:
7)Explain:
It will give only plan time.This won't execute query explain select * from emp;
cost=0.00 .... 1.10 rows=10 width=4
show cpu-tuple-cost;
show seq-page-cost;
8)Explain analyze:
This will execute query.Explain analyze will give planning time and execution time.
9)Autovacuum:
PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands.
Data loading:
=========
EDB loader is using to import CSV data.
copy from------>import
copy To--------->Export
Examlpe:
1)copy emp(id,name) to /temp/emp.csv csv Header;
2)copy id-test from 'temp/emp.csv' csv Header;
==>csv is in binary format since this will be 95% faster than insert.
==>Which will updates statistics (system_tables) (or) That autovaccum will update after configured time,Then only we can see updated statistics.
2)Analyze verbose:
syntax:analyze verbose <table-name>
==> shows number of pages and Number of live records(usually deleted rows wont get cleaned up immediately once we call Analyze then only dead rows will cleaned up).
3)vacuum:
Sysntax: Vacuum <table-name>
This will removes dead rows. Main use of vacuum is delete dead row which will increase performance.
How to view hidden(deleted) fields of all tables.
Ex: select c+id,* from emp;
output:
c+id id
(0,1) 1
(0,2) 2
-----
(0,255) 255
(1,1) 256
Note: If we remove dead rows from table we can achieve "good performance".
4)Vacuum full:
vacuum full will gives returns disk space to disk.
Note: In PostgerSQL each row will have trasaction Id:
How to view transcation id of each row of table?
Answer: select xmin,c+id,* from emp;
5)Vacuum freeze:
Under one transaction we can inserts 4 milion.If we insert above 4 milions in this case 4 milion are inserted then very first one will replaced with last row(4 milion +1).
To over come above issue vacuum freeze will be run by autovacuum for every 2 million records.
6)Vacuum analyze:
7)Explain:
It will give only plan time.This won't execute query explain select * from emp;
cost=0.00 .... 1.10 rows=10 width=4
show cpu-tuple-cost;
show seq-page-cost;
8)Explain analyze:
This will execute query.Explain analyze will give planning time and execution time.
9)Autovacuum:
PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands.
Data loading:
=========
EDB loader is using to import CSV data.
copy from------>import
copy To--------->Export
Examlpe:
1)copy emp(id,name) to /temp/emp.csv csv Header;
2)copy id-test from 'temp/emp.csv' csv Header;
==>csv is in binary format since this will be 95% faster than insert.
also see below
No comments:
Post a Comment