Re: [HACKERS] [ADMIN] Security question : Database access control
edit *pg_hba.conf * # Allow any user on the local system to connect to any # database under any username, but only via an IP connection: host all 127.0.0.1 255.255.255.255trust # The same, over Unix-socket connections: localall trust what about reading pg_hba.conf comments? localall md5 or *my nightmare *a cygwin on Win 98 everybody can can access everything :- /me shrugs i don't use cygwin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Improve perfomance for index search ANY(ARRAY[]) condition with single item
Hello,
The problems I tried to solve here:
1. Improve perfomance for index search ANY(ARRAY[...]) condition with
single item
2. I saw tons of users code like: if len(array) == 1: sql +=
'{}'.format(array[0]) else: sql += 'ANY(ARRAY[{}])'.format(array)
So there will be less lines of code and it will be clearer.
3. Confusing moment that "IN" works well with single item, and
"ANY(ARRAY[])" doesn't without any real reason.
The problem was discussed on stackoverflow:
https://stackoverflow.com/questions/45061966/index-usage-with-single-item-anyarray
That's my first patch so I will be grateful for constructive criticism.
---
CREATE TABLE public.t (id serial, a integer, b integer);
INSERT INTO t(a, b)
SELECT round(random()*1000), round(random()*1000)
FROM generate_series(1, 100);
CREATE INDEX "i_1" ON public.t USING btree (a, b);
CREATE INDEX "i_2" ON public.t USING btree (b);
---
If "a = 50" in the first query, everything is ok, appropriate index "i_1"
is used:
SELECT * FROM t WHERE a = 50 ORDER BY b LIMIT 1
"Limit (cost=0.42..4.03 rows=1 width=12) (actual time=0.085..0.085 rows=1
loops=1)"
" Buffers: shared hit=1 read=3"
" -> Index Scan using i_1 on t (cost=0.42..4683.12 rows=1300 width=12)
(actual time=0.084..0.084 rows=1 loops=1)"
"Index Cond: (a = 50)"
"Buffers: shared hit=1 read=3"
"Planning time: 0.637 ms"
"Execution time: 0.114 ms"
---
With "a IN (50)" result is the same:
SELECT * FROM t WHERE a IN (50) ORDER BY b LIMIT 1
"Limit (cost=0.42..4.03 rows=1 width=12) (actual time=0.058..0.058 rows=1
loops=1)"
" Buffers: shared hit=4"
" -> Index Scan using i_1 on t (cost=0.42..4683.12 rows=1300 width=12)
(actual time=0.056..0.056 rows=1 loops=1)"
"Index Cond: (a = 50)"
"Buffers: shared hit=4"
"Planning time: 0.287 ms"
"Execution time: 0.105 ms"
---
The problem is when I try to use "a = ANY(ARRAY[50])". Wrong index "i_2" is
used instead of "i_1" and execution time becomes x25 longer:
SELECT * FROM t WHERE a = ANY(ARRAY[50]) ORDER BY b LIMIT 1
"Limit (cost=0.42..38.00 rows=1 width=12) (actual time=2.591..2.591 rows=1
loops=1)"
" Buffers: shared hit=491 read=4"
" -> Index Scan using i_2 on t (cost=0.42..48853.65 rows=1300 width=12)
(actual time=2.588..2.588 rows=1 loops=1)"
"Filter: (a = ANY ('{50}'::integer[]))"
"Rows Removed by Filter: 520"
"Buffers: shared hit=491 read=4"
"Planning time: 0.251 ms"
"Execution time: 2.627 ms"
improve-single-item-array.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GSoC 2015: SP-GIST for geometrical objects
Hello, I am Dmitrii, student of Moscow Institute of Physics and Technology Abstract: I chose project "Indexing prolonged geometrical objects (i.e. boxes, circles, polygons, not points) with SP-GiST by mapping to 4d-space". According to the presentation https://www.pgcon.org/2011/schedule/attachments/197_pgcon-2011.pdf SP-GIST 3 times faster than GiST in some cases. But GIST supports geometrical data types: box, circle, polygon with operators: && &> &< &<| >> << <<| <@ @> @ |&> |>> ~ ~= Popular spatial extension PostGIS doesn't include SP-GIST, but has a lot of geometrical features. Project details: After meeting with Alexander Korotkov, I wrote some plan. Using of K-D-tree and Quadtree in building index for geometrical data types can increase speed of search in some cases. The main idea is representing 2-D geometrical objects in their bounding box. Set of 2-D boxes is 4-D space. New _ops will work with points from 4-D space, for example kd_box_ops, quad_circle_ops and will support all geometrical operators. After conversion object to their bounding box algo has set of tuples (x1, y1, x2, y2). Our goal is separate this space the most equally. If we talk about K-D-tree, on first step K-D-tree algorithm will split space in 2 parts by the first coordinate, in next step by the second coordinate etc., after 4-th coordinate we repeat this procedure. At the end we have index at geometrical objects and use traversal tree for every search operator. Postgresql has already has realization ideas of MBR in gist/gistproc.c. So I will transfer this realization to other type of tree. Of cource, I assume that SP-GIST can be not the best decision of this problem. So after testing this clear methods, I will try to find more effective way. Maybe with using combination of different spatial tree structures. Project Schedule: until May 25 Read documentation and source code, clarify details of implementation. 1st month Implement new '_ops' with all geometrical operators for box, circle, polygon 2nd month Research new methods for increase speed of geometrical query 3rd month Final refactoring, testing and submitting a patch. Links: http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html - about GIST https://toster.ru/q/27135#answer_110197 - people need SP-GIST for cubes http://www.slideshare.net/profyclub_ru/o-lt - presentation about indexes http://pgconf.ru/static/presentations/2015/korotkov_spatial.pdf - working with geo objects
[HACKERS] Re[2]: [HACKERS] GSoC 2015: SP-GIST for geometrical objects
>On Mar 27, 2015 11:08 AM, "Dima Ivanovskiy" < dima...@mail.ru > wrote: >> >> Hello, I am Dmitrii, student of Moscow Institute of Physics and Technology >> >> Abstract: >> >> I chose project "Indexing prolonged geometrical objects (i.e. boxes, >> circles, polygons, not points) with SP-GiST by mapping to 4d-space". >> According to the presentation >> https://www.pgcon.org/2011/schedule/attachments/197_pgcon-2011.pdf >> SP-GIST 3 times faster than GiST in some cases. But GIST supports >> geometrical data types: >> box, circle, polygon with operators: && &> &< &<| >> << <<| <@ @> @ |&> |>> >> ~ ~= >> Popular spatial extension PostGIS doesn't include SP-GIST, but has a lot of >> geometrical features. >> >> Project details: >> >> After meeting with Alexander Korotkov, I wrote some plan. >> Using of K-D-tree and Quadtree in building index for geometrical data types >> can increase speed of search in some cases. >> The main idea is representing 2-D geometrical objects in their bounding box. >> Set of 2-D boxes is 4-D space. >> New _ops will work with points from 4-D space, for example kd_box_ops, >> quad_circle_ops and will support all geometrical operators. >> After conversion object to their bounding box algo has set of tuples (x1, >> y1, x2, y2). >> Our goal is separate this space the most equally. If we talk about K-D-tree, >> on first step K-D-tree algorithm will split space in 2 parts by the first >> coordinate, in next step by the second coordinate etc., after 4-th >> coordinate we repeat this procedure. >> At the end we have index at geometrical objects and use traversal tree for >> every search operator. >> >> Postgresql has already has realization ideas of MBR in gist/gistproc.c. So I >> will transfer this realization to other type of tree. >> >> Of cource, I assume that SP-GIST can be not the best decision of this >> problem. So after testing this clear methods, I will try to find more >> effective way. Maybe with using combination of different spatial tree >> structures. >> >> Project Schedule: >> >> until May 25 >> >> Read documentation and source code, clarify details of implementation. >> >> 1st month >> >> Implement new '_ops' with all geometrical operators for box, circle, polygon >> >> 2nd month >> >> Research new methods for increase speed of geometrical query >> >> 3rd month >> >> Final refactoring, testing and submitting a patch. >> >> >> Links: >> >> http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html - about GIST >> https://toster.ru/q/27135#answer_110197 - people need SP-GIST for cubes >> http://www.slideshare.net/profyclub_ru/o-lt - presentation about indexes >> http://pgconf.ru/static/presentations/2015/korotkov_spatial.pdf - working >> with geo objects >> >Nice proposal. >Dynamic Kdtrees can perform badly as the splitting median can get way off as >updates are coming. What are your thoughts about that? >Also what's up with the 4d space? I don't quite get it. These types are 2 or 3 >dimensions. I read spgist README one more time . I didn't find the mechanism for maintaining good balance after updates. I think we can use Bkd-Tree, https://www.cs.duke.edu/~pankaj/publications/papers/bkd-sstd.pdf . But It can be not the best solving. I include Research time in 2nd month of timeline. About 4d space. All these types are 2 dimensional. Just as i n R-tree object is approximated by MBR. MBR for 2d-objects can be mapped to 4d-point. More general, nd-object MBR can be mapped into 2nd-point.
