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