Re: [GENERAL] Prefix search on all hstore values
On Wed, Nov 27, 2013 at 6:49 PM, Albert Chern albert.ch...@gmail.com wrote: I have an hstore column that stores a string in several arbitrary languages, so something like this: en = string in english, zh = string in chinese, fr = string in french Is it possible to construct an index that can be used to determine if a query string is a prefix of ANY of the values in the hstore? From reading the documentation the closest I've gotten is a gin index after converting the values to an array, but that doesn't seem to work with prefix searching. Any pointers would be much appreciated! The idea is to de-normalize the hstore_column to an assisting table with 2 columns: original_record_id, hstore_column_value. And to create a btree index on hstore_column_value that will effectively be used in prefix search. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Prefix search on all hstore values
Hi!
Full-text search has this feature.
# select to_tsvector('en_name=yes, fr_name=oui'::hstore::text) @@ 'en:*';
?column?
--
t
or (index only keys)
select to_tsvector(akeys('en_name=yes, fr_name=oui'::hstore)::text) @@ 'en:*';
?column?
--
t
To speed up this queries you use functional indexes.
Albert Chern wrote:
Hi,
I have an hstore column that stores a string in several arbitrary languages, so
something like this:
en = string in english, zh = string in chinese, fr = string in
french
Is it possible to construct an index that can be used to determine if a query
string is a prefix of ANY of the values in the hstore? From reading the
documentation the closest I've gotten is a gin index after converting the values
to an array, but that doesn't seem to work with prefix searching. Any pointers
would be much appreciated!
Thanks,
Albert
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Prefix search on all hstore values
On Thu, Nov 28, 2013 at 12:44 AM, Teodor Sigaev teo...@sigaev.ru wrote:
Full-text search has this feature.
# select to_tsvector('en_name=yes, fr_name=oui'::hstore::text) @@ 'en:*';
?column?
--
t
or (index only keys)
select to_tsvector(akeys('en_name=yes, fr_name=oui'::hstore)::text) @@
'en:*';
?column?
--
t
To speed up this queries you use functional indexes.
It wont work. The OP needs to search by values prefixes, not by any
separate word in the hstore.
# select to_tsvector('en_name=oh yes, fr_name=oui'::hstore::text) @@ 'ye:*';
?column?
--
t
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Prefix search on all hstore values
Thanks for the suggestions!
My requirements can be relaxed to full text search, but the problem I had
with that approach is I have strings in Chinese, and postgres doesn't seem
to support it. Calling to_tsvector() on Chinese characters always returns
an empty vector.
A separate table will definitely work, but I was hoping for something more
straightforward. I'll use that approach if necessary though.
On Thu, Nov 28, 2013 at 4:51 PM, Sergey Konoplev gray...@gmail.com wrote:
On Thu, Nov 28, 2013 at 12:44 AM, Teodor Sigaev teo...@sigaev.ru wrote:
Full-text search has this feature.
# select to_tsvector('en_name=yes, fr_name=oui'::hstore::text) @@
'en:*';
?column?
--
t
or (index only keys)
select to_tsvector(akeys('en_name=yes, fr_name=oui'::hstore)::text) @@
'en:*';
?column?
--
t
To speed up this queries you use functional indexes.
It wont work. The OP needs to search by values prefixes, not by any
separate word in the hstore.
# select to_tsvector('en_name=oh yes, fr_name=oui'::hstore::text) @@
'ye:*';
?column?
--
t
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com
Re: [GENERAL] Prefix search on all hstore values
My requirements can be relaxed to full text search, but the problem I had with that approach is I have strings in Chinese, and postgres doesn't seem to support it. Calling to_tsvector() on Chinese characters always returns an empty vector. Hm, check your locale settings. AFAIK, somebody uses FTS with Chinese language. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Will be Wildspeed inside Postgresql contrib?
Hi all, I would like to ask you a question: Is there any reason to not include Wildspeed in Postgresql contrib section? We are using it in some projects and it is very tedious to download and install it from source. I read some email opinions several years ago (I don't remember where and didn't find them again) which questioned its usability. I would like to say that on linguistic corpus search systems it is an extremely useful Postgresql addon. In such systems each word is inside a table row itself (sometimes going with its part-of-speech tag and/or lemma inside other fields of the same row) and prefix, suffix and/or infix queries are frequent. Moreover, data is not usually updated, that is, data is inserted and indexed once and, after then, only select queries take place so, generally, index sizes and indexation time don't matter if queries are answered fast. By other hand, users of such systems usually need that full-text database capabilities must be deactivated. They don't need stemming, synonyms, ..., and even it is not rare that they search for prefixes/suffixes of less than three letters. I know linguistic search systems aren't what most of you have in mind, but it could be interesting if Postgresql could include this functionallity by default as well so, is there any plan to include Wildspeed on Postgresql contrib section? Thank you in advance. Regards, -- Mario Barcala http://www.mario.barcala.name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will be Wildspeed inside Postgresql contrib?
2013/11/28 Mario Barcala em...@mario.barcala.name Wildspeed Same functionality has pg_trgm contrib module http://www.postgresql.org/docs/9.3/static/pgtrgm.html Regards Pavel
Re: [GENERAL] Will be Wildspeed inside Postgresql contrib?
And have both the same performance about searching speed? Is there any comparative and/or analysis? Thank you, Pavel Stehule wrote: [...] Same functionality has pg_trgm contrib module -- Mario Barcala http://www.mario.barcala.name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Will be Wildspeed inside Postgresql contrib?
2013/11/28 Mario Barcala em...@mario.barcala.name And have both the same performance about searching speed? Is there any comparative and/or analysis? I don't know about any performance analysis. I remember so wildspeed was not merged to contrib due high large index size. I expect similar performance. Pavel Thank you, Pavel Stehule wrote: [...] Same functionality has pg_trgm contrib module -- Mario Barcala http://www.mario.barcala.name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
David Johnston pol...@yahoo.com writes: Zev Benjamin wrote It appears that unnest, when called on a multi-dimensional array, effectively flattens the array first. For example: ... Multidimensional arrays do have shortcomings in the current implementation of which this is one. I'm not sure, though, if there is anything substantial and centralized in the docs so pertaining. It might be worth explaining that this is a consequence of the fact that Postgres treats all arrays over the same element type as being of the same data type --- that is, 1-D and 2-D arrays are not distinguished by the type system. Thus, when the polymorphic function unnest(anyarray) returns setof anyelement is applied to an integer array, it must return a series of integers; not a series of lower-dimensional arrays. There have been some discussions over whether this could be changed without a backwards-compatibility disaster, but nobody sees how. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
On Wed, Nov 27, 2013 at 11:28 PM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION
postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
reduce_dim
{1,2}
{2,3}
(2 rows)
Hi Pavel,
I hope you don't mind, I took the liberty of adding your nifty function to
the Postgresql Wiki at
https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
Feel free to edit directly or suggest any changes to it.
Cheers,
Bricklen
Re: [GENERAL] unnest on multi-dimensional arrays
2013/11/28 bricklen brick...@gmail.com
On Wed, Nov 27, 2013 at 11:28 PM, Pavel Stehule
pavel.steh...@gmail.comwrote:
Hello
postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION
postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
reduce_dim
{1,2}
{2,3}
(2 rows)
Hi Pavel,
I hope you don't mind, I took the liberty of adding your nifty function to
the Postgresql Wiki at
https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
Feel free to edit directly or suggest any changes to it.
+1
Pavel
Cheers,
Bricklen
[GENERAL] query creates a huge toast tables
Hi, This query (postgis) didn't endup after 4 hoursand eat 40gig of disk space SELECT id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as geom32198_s into potentialite FROM province as a, potentialite_tmp as b; I stopped the execution and cancel request sent appears, but after an hour the request wasn't stopped yet. So I killed the process and restat postgres. The database is up and running but I didn't get the 40gig of disk space back. And only 5gig remains on the server It is this table that is so huge pg_toast.pg_toast_11037520 I tried vaccuumdb without success. Vacuumdb full didn't work because only 5 gig left on the server What can I do to get the disk space back ? Can I simply drop pg_toast.pg_toast_11037520? I'm using PostgreSQL 9.1.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit POSTGIS=1.5.3 GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.8.0, 6 March 2012 LIBXML=2.7.6 USE_STATS thanks in advance for your help Steve Steve Toutant, M. Sc. Analyste en géomatique Secteur environnement Direction de la santé environnementale et de la toxicologie Institut national de santé publique du Québec Tél.: 418 646-6777 poste 30015 Téléc.: 418 644-4593 steve.tout...@inspq.qc.ca http://www.inspq.qc.ca http://www.monclimatmasante.qc.ca Pour me rejoindre par courrier Service de l'infrastructure et du réseau Direction des technologies de l'information Ministère de la Sécurité Publique 2525 boul. Laurier, 2è étage Tour Laurentides Québec, G1V 2L2
[GENERAL] What query optimisations are included in Postgresql?
Are there documents specifying the query optimisations in Postgresql like the SQLite (http://www.sqlite.org/optoverview.html)? From the web, I can say, there are index and join optimisation, but are there anything others like Subquery flattening? thanks. Bentley. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What query optimisations are included in Postgresql?
On 11/28/2013 09:46 AM, N wrote: Are there documents specifying the query optimisations in Postgresql like the SQLite (http://www.sqlite.org/optoverview.html)? http://www.postgresql.org/docs/9.3/interactive/overview.html http://www.postgresql.org/docs/9.3/interactive/performance-tips.html From the web, I can say, there are index and join optimisation, but are there anything others like Subquery flattening? thanks. Bentley. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What query optimisations are included in Postgresql?
Thanks, but still not same. For example, is there subquery flattening , is there any other query re-write ? is there website or books have such detail ? On Thu, Nov 28, 2013 at 5:54 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On 11/28/2013 09:46 AM, N wrote: Are there documents specifying the query optimisations in Postgresql like the SQLite (http://www.sqlite.org/optoverview.html)? http://www.postgresql.org/docs/9.3/interactive/overview.html http://www.postgresql.org/docs/9.3/interactive/performance-tips.html From the web, I can say, there are index and join optimisation, but are there anything others like Subquery flattening? thanks. Bentley. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What query optimisations are included in Postgresql?
On 11/28/2013 10:27 AM, N wrote: Thanks, but still not same. For example, is there subquery flattening , is there any other query re-write ? is there website or books have such detail ? http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/optimizer/README;h=adaa07ee60eebef0199b6e7208f709e56a6411e8;hb=b7f59e6d3e7c10ef0e222ce8ee6d19e8be304e29 -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to write inside TEMP environment path
After spending all day trying all the suggestions to get past this error while trying to install PostgreSQL on Windows 7 64-bit, I finally was given one that works. The EnterpriseDB guys gave me a link to one of their pages, I entered the error msg in their search box which found this forum page which down the page a little tells you to get rid of an old erroneous (default) McAfee registry entry and put in another correct value. This works like a charm as I was able to get my PostgreSQl 9.x going on my Windows 7 64-bit. http://forums.enterprisedb.com/posts/list/3040.page
[GENERAL] Error pg_standby 'pg_standby' is not recognized as an internal or external command!!
Hello everyone. I'm using PostgreSQl 9.2 on the windows XP in recovery.conf use command *standby_mode = 'on' primary_conninfo = 'host=10.0.10.2 port=5432 user=postgres password = password' restore_command = 'copy 10.0.10.2\\archiver\\%f %p' restore_command = 'pg_standby -d -s 5 -t C:\pgsql.trigger.5442 10.0.10.2\\archiver\\%f %p %r 2standby.log' recovery_end_command = 'del C:\pgsql.trigger.5442'* and standby.log say 'pg_standby' is not recognized as an internal or external command, operable program or batch file. How to fix ? Thank ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-pg-standby-pg-standby-is-not-recognized-as-an-internal-or-external-command-tp5780795.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ???
Date: Fri, 22 Nov 2013 20:11:47 +0100 Subject: Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ??? From: t...@fuzzy.cz To: bwon...@hotmail.com CC: brick...@gmail.com; pgsql-general@postgresql.org On 19 Listopad 2013, 5:30, Brian Wong wrote: I've tried any work_mem value from 1gb all the way up to 40gb, with no effect on the error. I'd like to think of this problem as a server process memory (not the server's buffers) or client process memory issue, primarily because when we tested the error there was no other load whatsoever. Unfortunately, the error doesn't say what kinda memory ran out. Hi Brian, first of all, please don't top-post, especially if the previous response used bottom-post. Thank you ;-) Regarding the issue you're seeing: * Increasing work_mem in hope that it will make the issue go away is pointless. In case work_mem is too low, PostgreSQL will automatically spill the data to disk (e.g. it won't do a sort in memory, but will do a on-disk merge sort). It will never fail, and messages failed on request of size is actually coming from malloc, when requesting another chunk of memory from the OS. So you're hitting a OS-level memory limit. After changing the shared_buffers setting to 200MB, the developer has confirmed that the Out Of Memory error no longer happens. So thanks folks. Playing with work_mem was out of desperation. Postgresql simply giving the Out of memory error wasn't informative enough about the problem. For example, is it the server buffer, the server process, or the client process that's having a problem? Note: AFAIK the only operation that does not spill to disk, and may fail with OOM-like errors is hash aggregate. But by increasing the work_mem you're actually encouraging PostgreSQL to do this planning error. I see the query you're running is doing MAX() so it might be hitting this issue. How much data are you dealing with? How many groups are in the result? * Setting shared buffers to 18GB is almost certainly a bad choice. I'm yet to see a real-world database where shared_buffers over 8GB actually make a measurable difference. More is not always better, and you're actually reserving memory that can't be used for work_mem (so you're making the issue worse). Please, decrease shared_buffers to e.g. 4GB, then try to increase it and measure the performance difference. If you look at the documentation about how to configure shared_buffers, it is very unclear to me how I can maximize performance by allocating as much memory to the buffer as possible. On one hand, the documentation says I shouldn't go high on the shared_buffers setting. On the other hand, the more memory you allocate to the buffers, the better the performance is supposedly. So at least as of 9.1, this is annoying. I heard that starting from 9.2, this behavior changed dramatically? * So how much memory does the query allocate? Can you watch it over top/free to get an idea if it e.g. allocates all available memory, or if it allocates only 1GB and then fail, or something? * I believe you're hitting some sort of limit, imposed by the operating system. Please check ulimit and overcommit settings. * BTW the SO post you mentioned as a perfect match was talking about query executed over dblink - are you doing the same? If so, try to move the aggregation into the query (so that the aggregation happens on the other end). Nope we're not using dblink in this case. regards Tomas --- Original Message --- From: bricklen brick...@gmail.com Sent: November 18, 2013 7:25 PM To: Brian Wong bwon...@hotmail.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: out of memory DETAIL: Failed on request of size ??? On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong bwon...@hotmail.com wrote: We'd like to seek out your expertise on postgresql regarding this error that we're getting in an analytical database. Some specs: proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on. memory: 48GB OS: Oracle Enterprise Linux 6.3 postgresql version: 9.1.9 shared_buffers: 18GB After doing a lot of googling, I've tried setting FETCH_COUNT on psql AND/OR setting work_mem. I'm just not able to work around this issue, unless if I take most of the MAX() functions out but just one. What is your work_mem set to? Did testing show that shared_buffers set to 18GB was effective? That seems about 2 to 3 times beyond what you probably want.
