Postgresql Çalışan Aktif Sorgular

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

SELECT pg_stat_activity.current_query, pg_class.relname, pg_locks.* from pg_class, pg_locks, pg_stat_activity where pg_class.relfilenode=pg_locks.relation and pg_stat_activity.procpid=pg_locks.pid;

Log sort

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

cut -d field seperator, -f filedleri almak için 
sort:
-r reverse
-t field seperator
-k 2. fielde göre sırala
-n numerik
[root@vt02 pg_log]# head 1.txt| cut -d" " -f1,6 | sort -n -r -t ' ' -k 2
<172.16.42.34-2016-11-11 2.655
<172.16.42.37-2016-11-11 2.463
<172.16.42.41-2016-11-11 2.428
<172.16.42.34-2016-11-11 0.621
<172.16.42.37-2016-11-11 0.596
<172.16.42.41-2016-11-11 0.593
<172.16.42.45-2016-11-11 0.140
<172.16.42.34-2016-11-11 0.113
<172.16.42.37-2016-11-11 0.110
<172.16.42.41-2016-11-11 0.091

Postgresql Bir Tablodan Geri Dönüş

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

[root@vt02 ~]# su - postgres
-bash-3.2$ pg_restore --data-only --table=t_yay_kuruluslar /home/arsiv/arsiv_201301150659.sql > yay.pg

“vi yay.pg” ile copy içindeki t_yay_kuruluslar t_yay_kuruluslar_asil olarak değiştirilir

-bash-3.2$ psql
psql (9.0.4)
Type "help" for help.

postgres=# \c db
You are now connected to database "db".
xxx_db=# create TABLE t_yay_kuruluslar_asil as (select * from t_yay_kuruluslar);
xxx_db=# TRUNCATE t_yay_kuruluslar_asil ;
xxx_db=# UPDATE t_yay_kuruluslar t1 set adres=t2.adres from t_yay_kuruluslar_asil t2 where t1.yayinci_id=t2.yayinci_id;
xxx_db=# drop table t_yay_kuruluslar_asil ;

Postgresql Tekrar Tespiti

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

select count (ALL dosya_adi) as all_dosya_adi, count(DISTINCT dosya_adi) as dis_dosya_adi, count(*) as all_rows from t_kayit where dosya_adi ilike '%mp4';
select count (ALL dosya_adi) as all_dosya_adi, count(DISTINCT dosya_adi) as dis_dosya_adi, count(*) as all_rows from t_kayit where dosya_adi ilike '%mp3';

Veritabanı zaman tespiti

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

select CURRENT_TIME;

Veritabanı tablosunu CSV olarak dışarı almak

Ocak 28, 2019 § Yorum yok § Kalıcı bağlantı

copy (select kanal_kimlik_no, kanal_adi from t_kanallar where kayit_yaslandirma_periyodu =365) to '/tmp/365.txt' with CSV;

SQL’de Group by, having vs mantığı…

Aralık 30, 2013 § Yorum yok § Kalıcı bağlantı

SELECT ile dönen liste 3 parçaya ayrılabilir.
– Süzme işlemi (WHERE alt cümlesi ile yapılabilen)
– Sıralama işlemi (ORDER BY alt cümlesi ile yapılabilen)
– Geri dönen liste üzerinde yapılan işlemler (GROUP BY, HAVING, MAX, SUM, COUNT, MIN….vs.)

Bir SELECT cümlesi yazalım

SELECT Price
  FROM Customer
 WHERE Price < 100
   AND Price > 50

Bu cmleyi Price’a göre gruplandiralim:

  SELECT Count(*), Price
    FROM Customer
   WHERE Price < 100
     AND Price > 50
GROUP BY Price

Sonuçta Price bilgisi 100 ile 50 arasında olan Customer listesine düşük seviyeli liste diyelim. Bu düşük seviyeli listenin üzerine GRUOP BY ve COUNT işlemleri uygulandıktan sonra ortaya çıkan yeni listeye ise sonuç listesi adını verelim.

Bu bilgiler ışığında GROUP BY alt cümlesinin tanımı:
– Cümleden sonra parametre olarak belirtilen alanın veya alanların değerine göre düşük seviyeli listeyi parçalara ayırır, belirtilen alanın veya alanların kaç farklı değeri var ise o kadar düşük seviyeli liste oluşturur.

COUNT(*), MIN, MAX, AVERAGE gibi fonksiyonların nasıl çalıştıklarını anlamak için:
– Tüm bu fonksiyonlar, düşük seviyeli listenin tamamı üzerine çalışır. Eğer düşük seviyeli liste GRUOP BY ile bölünmüş ise, her bir parça için ayrı ayrı çalışırlar.

Yani son SQL cümlesinde yer alan Count(*) işlemi her Price değeri için bölünmüş olan düşük seviyeli listenin herbir parçası için çalışır. Ve ortaya sonuç listesi olarak, her parçanın içerisinde yer alan kayıt sayısı ve her parçanın ayıraçı konumundaki Price bilgisi çıkar. Örnek;

Count(*) - Price
    5            60
    7            70
... vb.




Örnek uygulama:;

  SELECT Count(*), Price, Name
    FROM Customer
   WHERE Price < 100
     AND Price > 50
GROUP BY Price

Yukarıki sorgu neden hata vermektedir:

Şimdi öncelikle düşük seviyeli listenin ne olduğunu bulalım;
– Price bilgisi 100 ile 50 arasında olan Customer bilgileri.. Id, Price, Name…vs.. vs.. Bu listeye ‘A’ listesi diyelim.

‘GRUOP BY’ işlemi olduğuna göre, elimizdeki düşük seviyeli ‘A’ listesi Price bilgisinin değerlerine göre bölümlere ayrılmıştır. Örnek olarak Price bilgisi 5 farklı değere sahip olsun elimizdeki ‘A’ listesi ‘A1, A2, A3, A4, A5′ şeklinde küçük listelere ayrılır.

Daha sonra COUNT(*) işlemi ile bu her bir küçük listenin adet bilgisi ile Price ve Name bilgisi sonuç listesine verilmiştir. Peki burada ki gariplik ne, neden hata veriyor. Sorun şu A listesinden A1′e geçiş sırasında her bir Price’a karşılık ‘n’ adet Name bilgisi oluşmuştur bu nedenle sonuç listesi oluşturulurken, Count bilgisi hesaplanmış, Price bilgisi tek olduğu için alınmış fakat hangi Name bilgisinin kullanılacağına karar verilememiştir.

Düşük Seviyeli Liste’nin son hali..

Id Price Name
1 60 Test60-1
2 60 Test60-2
3 70 Test70-1
4 70 Test70-2
5 80 Test80-1
6 90 Test90-1

‘Group By’ uygulandıktan sonraki düşük seviyeli listenin hali;

Price Alt Kümeler
60
Id Name
1 Test60-1
2 Test60-2
70
Id Name
1 Test70-1
2 Test70-2
80
Id Name
1 Test80-1
90
Id Name
1 Test90-1

İşte bu da son ipucu; SQL programlama dilinde herhangi bir liste oluşturabilmek için bir kademe altta yer alan listenin elemanları direkt olarak kullanılabilir ya da fonksiyonlar yardımı ile alt listelere ulaşılıp, toplam, en küçük, en büyük, ortalama.. vb. değerler elde edilebilir.

Bu yazı “http://esiyo.net/select-gruop-by-having/110” adresinden alınmış/özetlenmiştir.

psql’i Kullanışlı Yapmak

Ağustos 15, 2013 § Yorum yok § Kalıcı bağlantı

1-Postgresql’in “more” ile sonuçları göstermesinin nedeni default PAGER’ın more olarak ayarlı olmasıdır.
Bu durumu değiştirmek için

export PAGER="/usr/bin/less -isM"
veya
export PAGER="/usr/bin/less

kullanılabilir. Bunun kalıcı olması için ~/.bashrc içine eklenebilir.

2. ./psqlrc ile bazı ek fonksiyonlar kazanldırılabilir. Bu dosya her psql çalıştırıldığında okunmaktadır.
Benim Postgresql 9.1 için kullandığım .psqlrc dosyası (dosyapsqlrc_icerik)

-- vim: se ft=psql :

------------------------------------------------------------------------------
-- sources and inspiration:                                                                 --
-- http://opensourcedbms.com/dbms/psqlrc-psql-startup-file-for-postgres/    --
-- http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/               --
-- https://github.com/dlamotte/dotfiles/blob/master/psqlrc                  --
------------------------------------------------------------------------------


\set HISTFILE ~/.psql_history- :HOST - :USER -:DBNAME
\set HISTSIZE 2000


\echo '\nCurrent Host Server Date Time : '`date` '\n'

\echo 'Administrative queries:\n'

\echo '\t:activity\t-- Server activity'
\echo '\t:bloat\t\t-- show table and index bloat in your database ordered by most wasteful'
\echo '\t:blocking\t-- display queries holding locks other queries are waiting to be released'
\echo '\t:cache_hit\t-- calculates your cache hit rate (effective databases are at 99% and up)'
\echo '\t:conninfo\t-- Server connections'
\echo '\t:dbsize\t\t-- Database Size'
\echo '\t:index_size\t-- show the size of the indexes, descending by size'
\echo '\t:index_usage\t-- calculates your index hit rate (effective databases are at 99% and up)'
\echo '\t:locks1\t\t-- Lock info'
\echo '\t:locks2\t\t\t-- display queries with active locks'
\echo '\t:long_running_queries\t-- show all queries taking longer than five minutes ordered by duration'
\echo '\t:ps\t\t\t-- view active queries with execution time'
\echo '\t:seq_scans\t\t--show the count of seq_scans by table descending by order'
\echo '\t:settings\t-- Server Settings'
\echo '\t:tablesize\t-- Tables Size'
\echo '\t:total_index_size\t-- show the total size of the indexes in MB'
\echo '\t:unused_indexes\t\t-- Show unused and almost unused indexes, ordered by their size relative'
\echo '\t\t\t\t   to the number of index scans. Exclude indexes of very small tables (< 5 pages),'
\echo '\t\t\t\t   where the planner will almost invariably select a sequential scan, but may not'
\echo '\t\t\t\t   in the future as the table grows.'
\echo '\t:uptime\t\t-- Server uptime'
\echo '\t:uselesscol\t-- Useless columns (must be run as superuser)'
\echo '\t:waits\t\t-- Waiting queries'
\echo '\n'


\echo 'Help:\n'

\echo '\t:menu\t\t-- This help menu'
\echo '\t\\h\t\t-- Help with SQL commands'
\echo '\t\\?\t\t-- Help with psql commands\n'

-- Administration queries

\set menu '\\i ~/.psqlrc'

\set settings 'select name, setting,unit,context from pg_settings;'

\set locks1  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;'

\set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'

\set activity 'select datname, procpid, usename, application_name,client_addr, client_addr, client_port, current_query, waiting from pg_stat_activity;'

\set waits 'SELECT pg_stat_activity.procpid, pg_stat_activity.current_query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.current_query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'

\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'

-- Taken from https://github.com/heroku/heroku-pg-extras
-- via https://github.com/dlamotte/dotfiles/blob/master/psqlrc
\set bloat 'SELECT tablename as table_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname as index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'',''8.1'',''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1,2,3,4,5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;'
\set blocking 'select bl.pid as blocked_pid, ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration, kl.pid as blocking_pid, a.current_query as blocked_statement, now() - a.query_start as blocked_duration from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on bl.pid = a.procpid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on kl.pid = ka.procpid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;'
\set cache_hit 'SELECT ''index hit rate'' as name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT ''cache hit rate'' as name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;'
\set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;'
\set index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
\set locks2 'select pg_stat_activity.procpid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substr(pg_stat_activity.current_query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.current_query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.procpid and pg_locks.mode = ''ExclusiveLock'' order by query_start;'
\set long_running_queries 'SELECT procpid, now() - pg_stat_activity.query_start AS duration, current_query AS query FROM pg_stat_activity WHERE pg_stat_activity.current_query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'
\set ps 'select procpid, application_name as source, age(now(),query_start) as running_for, waiting, current_query as query from pg_stat_activity where current_query <> ''<insufficient privilege>'' AND current_query <> ''<IDLE>'' and procpid <> pg_backend_pid() order by 3 desc;'
\set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0;'
\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
--

-- Development queries:


Postgresql’de rastgele sayı oluşturma

Temmuz 30, 2013 § Yorum yok § Kalıcı bağlantı

Postgresql random() fonksiyonu ile 0-1 arasında rastgele sayı döner.

5 tane rastgele sayı dönelim:

SELECT random() FROM generate_series(1,5);
      random       
-------------------
 0.595817462075502
 0.350072300527245
 0.989073566626757
 0.700899163261056
 0.940509075298905
(5 ROWS)

Rastgele sayıları 1-100 arasına getirelim

SELECT random() * 99 + 1 FROM generate_series(1,5);
     ?COLUMN?     
------------------
 45.0829070857726
 96.3123500789516
 30.1867960244417
 63.0484761050902
 44.2645903658122
(5 ROWS)

trunc ile sayıları yuvarlayalım

SELECT trunc(random() * 99 + 1) FROM generate_series(1,5);
 trunc 
-------
    21
     6
     2
    57
    58
(5 ROWS)

Şimdi bu işleri yapan bir fonksiyon yazalım

CREATE OR REPLACE FUNCTION get_random_number(INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
    start_int ALIAS FOR $1;
    end_int ALIAS FOR $2;
BEGIN
    RETURN trunc(random() * (end_int-start_int) + start_int);
END;
$$ LANGUAGE 'plpgsql' STRICT;

Şimdi deneyelim

SELECT get_random_number(1, 100); 
get_random_number 
-------------------
                11
(1 ROW)
 
SELECT get_random_number(45, 46);
 get_random_number 
-------------------
                45
(1 ROW)
 
SELECT get_random_number(300, 400);
 get_random_number 
-------------------
               372
(1 ROW)
 
SELECT MIN(get_random_number(100, 1000)), MAX(get_random_number(100, 1000)) FROM generate_series(1,100000);
 MIN | MAX 
-----+-----
 100 | 999
(1 ROW)

Postgresql table partition yapmak!

Temmuz 30, 2013 § Yorum yok § Kalıcı bağlantı

1-Master table yarat
2-Child tables yarat (constraint kesişimi olmamalı)
3-İndexleri oluştur
4-Trigger function yaz
5-“Constraint exclusion”ı enable yap.

1-Master table yarat

CREATE TABLE impressions_by_day (
	advertiser_id INTEGER NOT NULL,
	DAY DATE NOT NULL DEFAULT CURRENT_DATE,
	impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, DAY)
);

2-Child tables yarat

CREATE TABLE impressions_by_day_y2009m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-01-01' AND DAY < DATE '2009-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-03-01' AND DAY < DATE '2009-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-05-01' AND DAY < DATE '2009-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-07-01' AND DAY < DATE '2009-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-09-01' AND DAY < DATE '2009-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2009m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2009-11-01' AND DAY < DATE '2010-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-01-01' AND DAY < DATE '2010-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-03-01' AND DAY < DATE '2010-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-05-01' AND DAY < DATE '2010-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-07-01' AND DAY < DATE '2010-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-09-01' AND DAY < DATE '2010-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2010m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2010-11-01' AND DAY < DATE '2011-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-01-01' AND DAY < DATE '2011-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-03-01' AND DAY < DATE '2011-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-05-01' AND DAY < DATE '2011-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-07-01' AND DAY < DATE '2011-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-09-01' AND DAY < DATE '2011-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2011m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2011-11-01' AND DAY < DATE '2012-01-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m1ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-01-01' AND DAY < DATE '2012-03-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m3ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-03-01' AND DAY < DATE '2012-05-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m5ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-05-01' AND DAY < DATE '2012-07-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m7ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-07-01' AND DAY < DATE '2012-09-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m9ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-09-01' AND DAY < DATE '2012-11-01' )
) INHERITS (impressions_by_day);
 
CREATE TABLE impressions_by_day_y2012m11ms2 (
	PRIMARY KEY (advertiser_id, DAY), 
	CHECK ( DAY >= DATE '2012-11-01' AND DAY < DATE '2013-01-01' )
) INHERITS (impressions_by_day);

3-İndexleri oluştur

CREATE INDEX impressions_by_day_y2009m1ms2_day ON impressions_by_day_y2009m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m3ms2_day ON impressions_by_day_y2009m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m5ms2_day ON impressions_by_day_y2009m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m7ms2_day ON impressions_by_day_y2009m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m9ms2_day ON impressions_by_day_y2009m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m11ms2_day ON impressions_by_day_y2009m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m1ms2_day ON impressions_by_day_y2010m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m3ms2_day ON impressions_by_day_y2010m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m5ms2_day ON impressions_by_day_y2010m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m7ms2_day ON impressions_by_day_y2010m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m9ms2_day ON impressions_by_day_y2010m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m11ms2_day ON impressions_by_day_y2010m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m1ms2_day ON impressions_by_day_y2011m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m3ms2_day ON impressions_by_day_y2011m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m5ms2_day ON impressions_by_day_y2011m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m7ms2_day ON impressions_by_day_y2011m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m9ms2_day ON impressions_by_day_y2011m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m11ms2_day ON impressions_by_day_y2011m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m1ms2_day ON impressions_by_day_y2012m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m5ms2_day ON impressions_by_day_y2012m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m7ms2_day ON impressions_by_day_y2012m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m9ms2_day ON impressions_by_day_y2012m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m11ms2_day ON impressions_by_day_y2012m11ms2 (DAY);

4-Triger function yaz
Triger fonksiyonu child tablelarla aynı şartlara göre yaratılmalıdır. Şartların tamamen aynı olması çok önemli.
Triger fonksiyonu:

CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.DAY >= DATE '2009-01-01' AND NEW.DAY < DATE '2009-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m1ms2 VALUES (NEW.*); 
	ELSIF ( NEW.DAY >= DATE '2009-03-01' AND NEW.DAY < DATE '2009-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-05-01' AND NEW.DAY < DATE '2009-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-07-01' AND NEW.DAY < DATE '2009-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-09-01' AND NEW.DAY < DATE '2009-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2009-11-01' AND NEW.DAY < DATE '2010-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-01-01' AND NEW.DAY < DATE '2010-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-03-01' AND NEW.DAY < DATE '2010-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-05-01' AND NEW.DAY < DATE '2010-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-07-01' AND NEW.DAY < DATE '2010-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-09-01' AND NEW.DAY < DATE '2010-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2010-11-01' AND NEW.DAY < DATE '2011-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2010m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-01-01' AND NEW.DAY < DATE '2011-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-03-01' AND NEW.DAY < DATE '2011-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-05-01' AND NEW.DAY < DATE '2011-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-07-01' AND NEW.DAY < DATE '2011-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-09-01' AND NEW.DAY < DATE '2011-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2011-11-01' AND NEW.DAY < DATE '2012-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2011m11ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-01-01' AND NEW.DAY < DATE '2012-03-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m1ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-03-01' AND NEW.DAY < DATE '2012-05-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m3ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-05-01' AND NEW.DAY < DATE '2012-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m5ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-07-01' AND NEW.DAY < DATE '2012-09-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m7ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-09-01' AND NEW.DAY < DATE '2012-11-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m9ms2 VALUES (NEW.*);
	ELSIF ( NEW.DAY >= DATE '2012-11-01' AND NEW.DAY < DATE '2013-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2012m11ms2 VALUES (NEW.*);
	ELSE
		RAISE EXCEPTION 'Date out of range.  Something wrong with the impressions_by_day_insert_trigger() function!';
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Triger:

CREATE TRIGGER insert_impressions_by_day_trigger 
	BEFORE INSERT ON impressions_by_day 
	FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

5-“Constraint exclusion”ı enable yap.
Partition yapısının çalışabilmesi için postgresql.conf dosyasındaki constraint_exclusion=on yapılmalıdır.

Veri boyutu arttığında index boyutu artacak ve index kullanımı işlevsel olmaktan çıkacaktır.
Ayrıca partition yapılması update ve insert hızını azaltırken select hızını arttırmaktadır.
Sonuçta partition kullanılıp kullanılmayacağına performans testi yapılarak karar verilmelidir.

Neredeyim ben!?

PostgreSQL Notları kategorisinde geziniyorsun.