This repository has been archived by the owner on May 26, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 71
/
postgresql_tips.tex
181 lines (109 loc) · 8.24 KB
/
postgresql_tips.tex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
\chapter{Полезные мелочи}
\begin{epigraphs}
\qitem{Быстро найти правильный ответ на трудный вопрос~--- ни с чем не сравнимое удовольствие}{Макс Фрай. Обжора-Хохотун}
\end{epigraphs}
\section{Введение}
Иногда возникают очень интересные проблемы по работе с PostgreSQL, которые при нахождении ответа поражают своей лаконичностью, красотой и простым исполнением. В данной главе я решил собрать интересные методы решения разных проблем, с которыми сталкиваются люди при работе с PostgreSQL.
\section{Мелочи}
\subsection{Размер объектов в базе данных}
Данный запрос показывает размер объектов в базе данных (например, таблиц и индексов).
\lstinputlisting[language=SQL,label=lst:snippets1,title=snippets/biggest\_relations.sql]{example_code/snippets/biggest_relations.sql}
Пример вывода:
\begin{lstlisting}[language=SQL,label=lst:snippets2,caption=Поиск самых больших объектов в БД. Пример вывода]
relation | size
------------------------+------------
public.accounts | 326 MB
public.accounts_pkey | 44 MB
public.history | 592 kB
public.tellers_pkey | 16 kB
public.branches_pkey | 16 kB
public.tellers | 16 kB
public.branches | 8192 bytes
\end{lstlisting}
\subsection{Размер самых больших таблиц}
Данный запрос показывает размер самых больших таблиц в базе данных.
\lstinputlisting[language=SQL,label=lst:snippets3,title=snippets/biggest\_tables.sql]{example_code/snippets/biggest_tables.sql}
Пример вывода:
\begin{lstlisting}[language=SQL,label=lst:snippets4,caption=Размер самых больших таблиц. Пример вывода]
relation | total_size
--------------------------------+------------
public.actions | 4249 MB
public.product_history_records | 197 MB
public.product_updates | 52 MB
public.import_products | 34 MB
public.products | 29 MB
public.visits | 25 MB
\end{lstlisting}
\subsection{<<Средний>> count}
Данный метод позволяет узнать приблизительное количество записей в таблице. Для огромных таблиц этот метод работает быстрее, чем обыкновенный count.
\lstinputlisting[language=SQL,label=lst:snippets5,title=snippets/count\_estimate.sql]{example_code/snippets/count_estimate.sql}
Пример:
\begin{lstlisting}[language=SQL,label=lst:snippets51,caption=<<Средний>> count. Пример]
CREATE TABLE foo (r double precision);
INSERT INTO foo SELECT random() FROM generate_series(1, 1000);
ANALYZE foo;
# SELECT count(*) FROM foo WHERE r < 0.1;
count
-------
92
(1 row)
# SELECT count_estimate('SELECT * FROM foo WHERE r < 0.1');
count_estimate
----------------
94
(1 row)
\end{lstlisting}
\subsection{Случайное число из диапазона}
Данный метод позволяет взять случайное число из указаного диапазона (целое или с плавающей запятой).
\lstinputlisting[language=SQL,label=lst:snippets8,title=snippets/random\_from\_range.sql]{example_code/snippets/random_from_range.sql}
Пример:
\begin{lstlisting}[language=SQL,label=lst:snippets9,caption=Случайное число из диапазона. Пример]
SELECT random(1,10)::int, random(1,10);
random | random
--------+------------------
6 | 5.11675184825435
(1 row)
SELECT random(1,10)::int, random(1,10);
random | random
--------+------------------
7 | 1.37060070643201
(1 row)
\end{lstlisting}
\subsection{Алгоритм Луна}
\href{http://en.wikipedia.org/wiki/Luhn\_algorithm}{Алгоритм Луна или формула Луна}~--- алгоритм вычисления контрольной цифры, получивший широкую популярность. Он используется, в частности, при первичной проверке номеров банковских пластиковых карт, номеров социального страхования в США и Канаде. Алгоритм был разработан сотрудником компании <<IBM>> Хансом Петером Луном и запатентован в 1960 году.
Контрольные цифры вообще и алгоритм Луна в частности предназначены для защиты от случайных ошибок, а не преднамеренных искажений данных.
Алгоритм Луна реализован на чистом SQL. Обратите внимание, что эта реализация является чисто арифметической.
\lstinputlisting[language=SQL,label=lst:snippets10,title=snippets/luhn\_algorithm.sql]{example_code/snippets/luhn_algorithm.sql}
Пример:
\begin{lstlisting}[language=SQL,label=lst:snippets11,caption=Алгоритм Луна. Пример]
Select luhn_verify(49927398716);
luhn_verify
-------------
t
(1 row)
Select luhn_verify(49927398714);
luhn_verify
-------------
f
(1 row)
\end{lstlisting}
\subsection{Выборка и сортировка по данному набору данных}
Выбор данных по определенному набору данных можно сделать с помощью обыкновенного \lstinline!IN!. Но как сделать подобную выборку и отсортировать данные в том же порядке, в котором передан набор данных? Например:
Дан набор: (2,6,4,10,25,7,9). Нужно получить найденные данные в таком же порядке т.~е. 2 2 2 6 6 4 4
\lstinputlisting[language=SQL,label=lst:snippets12,title=snippets/order\_like\_in.sql]{example_code/snippets/order_like_in.sql}
где
\lstinline!VALUES(3),(2),(6),(1),(4)!~--- наш набор данных
\lstinline!foo!~-- таблица, из которой идет выборка
\lstinline!foo.catalog_id!~--- поле, по которому ищем набор данных (замена \lstinline!foo.catalog_id IN(3,2,6,1,4)!)
\subsection{Quine~--- запрос который выводит сам себя}
Куайн, квайн (англ. quine)~--- компьютерная программа (частный случай метапрограммирования), которая выдаёт на выходе точную копию своего исходного текста.
\lstinputlisting[language=SQL,label=lst:snippets13,title=snippets/quine.sql]{example_code/snippets/quine.sql}
\subsection{Поиск дубликатов индексов}
Запрос находит индексы, созданные на одинаковый набор столбцов (такие индексы эквивалентны, а значит бесполезны).
\lstinputlisting[language=SQL,label=lst:snippets15,title=snippets/duplicate\_indexes.sql]{example_code/snippets/duplicate_indexes.sql}
\subsection{Размер и статистика использования индексов}
\lstinputlisting[language=SQL,label=lst:snippets16,title=snippets/indexes\_statustic.sql]{example_code/snippets/indexes_statustic.sql}
\subsection{Размер распухания (bloat) таблиц и индексов в базе данных}
\label{sec:snippets-bloating}
Запрос, который показывает <<приблизительный>> bloat (раздутие) таблиц и индексов в базе:
\lstinputlisting[language=SQL,label=lst:snippets17,title=snippets/bloating.sql]{example_code/snippets/bloating.sql}