Postgre SQL Postgre SQL Postgre SQL Postgre SQL
- Slides: 37
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL
Postgre. SQL Что нового в Postgre. SQL 8. 1 ?
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● ● SQL Двухфазный коммит (2 PC) PREPARE TRANSACTION COMMIT PREPARED ROLLBACK PREPARED JDBC драйвер поддержка XA (JTA)
Postgre. SQL Что нового в Postgre. SQL 8. 1? ● ● ● Very large Database Bitmap index (in memory) Оптимизация работы с несколькими индексами Bitmap Index Scan – Bitmap Heap Scan GUC параметр: enable_bitmapscan
Postgre. SQL Q 3 C sky indexing algorithm ● SAI Catalog Access Services
Postgre. SQL Q 3 C Sky indexing algorithm
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● ● Very Large Database Table Partitioning Наследование таблиц – table inheritance Улучшение в планировщике CONSTRAIN EXCLUSION GUC параметр: constraint_exclusion (off)
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Table Partitioning (Пример) Создаем таблицы create table a ( i int primary key); create table a 1( check (i >=0 and i<=2000) ) inherits(a); create table a 2( check (i >=2001 and i<=4000) ) inherits(a); create table a 3( check (i >=4001 and i<=6000) ) inherits(a); create index a 1_idx on a 1(i); create index a 2_idx on a 2(i); create index a 3_idx on a 3(i);
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Table Partitioning Заполняем таблицы for ((i=0; i<2000; i++)) do echo $i; done| psql test -c "copy a 1 from stdin; " for ((i=2001; i<4000; i++)) do echo $i; done| psql test -c "copy a 2 from stdin; " for ((i=4001; i<6000; i++)) do echo $i; done| psql test -c "copy a 3 from stdin; "
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Table Partitioning Без CONSTRAINT EXCLUSION test=# explain select * from a where i = 10; QUERY PLAN Result (cost=0. 00. . 42. 70 rows=34 width=4) -> Append (cost=0. 00. . 42. 70 rows=34 width=4) -> Index Scan using a_pkey on a (cost=0. 00. . 4. 82 rows=1 width=4) Index Cond: (i = 10) -> Bitmap Heap Scan on a 1 a (cost=2. 04. . 12. 63 rows=11 width=4) Recheck Cond: (i = 10) -> Bitmap Index Scan on a 1_idx (cost=0. 00. . 2. 04 rows=11 width=0) Index Cond: (i = 10) -> Bitmap Heap Scan on a 2 a (cost=2. 04. . 12. 63 rows=11 width=4) Recheck Cond: (i = 10) -> Bitmap Index Scan on a 2_idx (cost=0. 00. . 2. 04 rows=11 width=0) Index Cond: (i = 10) -> Bitmap Heap Scan on a 3 a (cost=2. 04. . 12. 63 rows=11 width=4) Recheck Cond: (i = 10) -> Bitmap Index Scan on a 3_idx (cost=0. 00. . 2. 04 rows=11 width=0) Index Cond: (i = 10)
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Table Partitioning CONSTRAINT EXCLUSION !!! test=# explain select * from a where i = 10; QUERY PLAN Result (cost=0. 00. . 17. 45 rows=12 width=4) -> Append (cost=0. 00. . 17. 45 rows=12 width=4) -> Index Scan using a_pkey on a (cost=0. 00. . 4. 82 rows=1 width=4) Index Cond: (i = 10) -> a 1 Bitmap Heap Scan on Recheck Cond: (i = 10) -> width=0) (8 rows) Bitmap Index Scan on a (cost=2. 04. . 12. 63 rows=11 width=4) a 1_idx Index Cond: (i = 10) (cost=0. 00. . 2. 04 rows=11
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Улучшенная поддержка функций IN, OUT, INOUT, совместимость с ORACLE CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$ BEGIN y : = y + 5; z : = x + 5; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; SELECT foo(10, 20); foo ----(25, 15) (1 row) SELECT (foo(10, 20)). *; y | z ----+---25 | 15 (1 row)
Postgre. SQL Что нового в Postgre. SQL 8. 1 ? ● ● Интегрированный autovacuum MVCC -> VACUUM # select xmin, xmax, i from a where i=5999; xmin | xmax | i ----+------+-----185789 | 0 | 5999 # begin; BEGIN =# delete from a where i=5999; DELETE 1 # select xmin, xmax, i from a where i=5999; xmin | xmax | i --------+-----185789 | 185809 | 5999
Postgre. SQL Что ожидается в Postgre. SQL 8. 2 ?
Postgre. SQL ● ● ● Sony Online Entertainment Enterprise DB, 1. 5 mln. Oracle -> Postgre. SQL 8. 1 SUN Microsystems Solaris 10 поддержка 24 x 7 Beeline (Вымпелком) SAI CAS - Catalog Access Service Терабайты vo. astronet. ru Rx 1620 HP RUSSIA Itanium 2, Linux 2. 6
Postgre. SQL Благодарности ● ● РФФИ Astronet (www. astronet. ru), 05 -07 -90225 -в Научная сеть (nature. web. ru), 03 -07 -90187 -в HP Russia
- Postgre serial
- How to pronounce postgre
- Postgre
- Postgre
- Difference between oracle and pl sql
- Pl sql unit testing
- Azure sql dtu limits
- Sql security best practices
- Wait stats in sql server
- Argent job scheduler
- Codeapillar troubleshooting
- Yymmddhhmmss
- Sql que es
- Types of subqueries in sql
- Navision sql performance tuning
- Sql server in memory oltp
- A data warehouse is a logical collection of information
- Sql server 101
- Sql server dehardening
- Basic constraints in sql
- Subprograms in pl sql
- Sybase sql injection
- Difference between procedure and function in sql
- Php sql tutorial
- Sqlhammer
- Access sorgu örnekleri
- My sql software
- Sql empty in clause
- Bahasa sql merupakan generasi bahasa pemrograman ke-4
- Northwind database query exercises
- Pl sql architecture
- Sql injection
- Sql design patterns
- "sql" "join"
- Sql exercise
- Sql
- Composite data types
- Avax vs cosmos