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
Download Postgre SQL installer Download Postgre SQL https
Postgre Sql Postgre Sql www specialist ru CHECK
Postgre SQLIE An Imagehandling Extension for Postgre SQL
SQL SQL SELECT SQL INSERT SQL UPDATE SQL
My SQL Vs Postgre SQL comparison from a
My SQL Vs Postgre SQL for a small
Conceptual Architecture of Postgre SQL Pop SQL Andrew
Postgre SQL a discussion for SQL Server Professionals
Conceptual Architecture of Postgre SQL Pop SQL Andrew
Programming in postgre SQL with PLpg SQL Procedural
Programming in postgre SQL with PLpg SQL Procedural
SQL Syntaks SQL Hi A SQL SQL Structured
SQL Syntaks SQL Hi A SQL SQL Structured
PLSQL l SQL SQL DBMS SQL SQL IF
SQL Syntaks SQL Hi A SQL SQL Structured
Using Partial Indexes with Postgre SQL By Lloyd
Summer 2007 The 1 st Postgre SQL Enterpriselevel
Conceptual Architecture of Postgre SQL SQueueL Khurrum A
Postgre SQL Introduction Most Advanced Opensource ORDBMS 1997
PHP My SQLite Postgre SQL Oracle OCI 8
Introduction to PHP and Postgre SQL CSC 436
Open ACS Porting Oracle Applications to Postgre SQL
Serial Number and Indexing in Postgre SQL University
View in Postgre SQL University of Tehran ECE
Proposal for Postgre SQL as State DB for
Postgre SQL S 511 SLIS Postgresql server PHP
JDBC Postgre SQL Useful JDBC Links Getting Started
Postgre SQL Bye the dolphin Hello the elephant
Redundancy Control For Postgre SQL SQueueL Khurrum A
Java Servlet Postgre SQL 7 HTML http www
Postgre SQL as a Document Storage for NET
Planner NTT postgresql org 20040726 1 Postgre SQL
Forensic Audit Logging for Postgre SQL Moshe Jacobson
1 Postgre SQL Ch 1 5 Learning to
Analza monost SBD Postgre SQL Post GIS pro
TLS DB Encrypted FS Postgre SQL http archives
Postgre SQL Temporal Data Christopher Browne Afilias Canada