[GENERAL] Query runtime strongly dependent on generated statistics (and fewer statistics are better?)

Hi, (I've sent this mail about month ago, but received no reply. So I'm trying again.) I've noticed that the selection of the executed query plan (and therefore query runtime) is dependent on the statistics generated by an ANALYZE run. As an demonstration, I chose the best runtime of 5 consecutiv

[GENERAL] BUG? Regular expression matching of optional character group at beginning of RE

Hi, I've noticed that regular expressions which are anchored at the beginning of the text but have an optional part at the beginning (e.g. '^(ge)?kommen$') are not evaluated correctly if there is an index on the column. Consider the following table: #+BEGIN_SRC sql CREATE TABLE annotation (

[GENERAL] Query runtime dependent on ANALYZE run

Hi, (I've sent a copy of this message to pgus-general in error. Sorry about that.) I've noticed that the selection of the executed query plan (and therefore query runtime) is dependent on the statistics generated by an ANALYZE run. As an demonstration, I chose the best runtime of 5 consecutive ru

Re: [GENERAL] Use true and false when bulk-exporting boolean data

Hi Alban, in the end I used a COPY statement with a query and a CASE statement as suggested by Henry. Cheers, Viktor Alban Hertroys wrote: > On 18 Oct 2011, at 17:54, Viktor Rosenfeld wrote: > > > I was looking for an easier, more automatic way, but I wrote a few > > script

Re: [GENERAL] Use true and false when bulk-exporting boolean data

, 2011 at 2:29 PM, Viktor Rosenfeld > wrote: > > > Hi, > > > > I need to move data from PostgreSQL to MonetDB and also bulk-import data > > into MonetDB that was bulk-exported from PostgreSQL by other people. My > > problem is that boolean data is exported by

[GENERAL] Use true and false when bulk-exporting boolean data

Hi, I need to move data from PostgreSQL to MonetDB and also bulk-import data into MonetDB that was bulk-exported from PostgreSQL by other people. My problem is that boolean data is exported by PostgreSQL using the values "t" and "f" (unquoted) and that MonetDB expects "true" and "false". Is there

[GENERAL] R-Trees in PostgreSQL

Hi, I'd like to create an R-Tree index on two numeric columns. As far as I know, PostgreSQL supports R-Trees via the GiST index class for some spatial types (box and the like). When I create a GiST index on two numeric columns, I get the error message: ERROR: data type numeric has no default

[GENERAL] could not identify an equality operator for type annotation (Was: could not find array type for data type character varying[])

PE annotation USING hash AS OPERATOR 1 =, FUNCTION 1 annotation_hash(rhs annotation) ; But then the error message changes to: ERROR: cache lookup failed for operator 34755 Any idea? Cheers, Viktor Sam Mason wrote: > On Wed, Oct 28, 2009 at 04:17:32PM +0100, Viktor Rosenfeld w

[GENERAL] could not find array type for data type character varying[]

Hi, I'm trying to aggregate a list of table attributes into an array. The actual code looks something like this: SELECT node_ref AS id, array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations ... GROUP BY id; I guess the minimal example that reproduces the error is: an

[GENERAL] Getting rid of UNION

Hi, the following query takes 13 seconds to run vs. 31 milliseconds for an (almost) equivalent query using UNION. The main penalty comes from two nestloops in the plan (http://explain.depesz.com/s/2o). Is this approach feasable and if so, what am I doing wrong? Also, is there a shorter idiom fo

[GENERAL] OT: Re: [sfpug] 10/13 SFPUG meeting, "The Mighty GUCS," video now available

And there I thought I was considerate when I didn't post during the week-end. It's also curious that "some gratitude is in order" while the developers owe us nothing. (Not that I think they do, I just think those two attitudes contradict each other.) @Christophe, I enjoyed your talk very much, p

Re: [GENERAL] different execution times of the same query

Hi, I suppose the same is true for the time logged with log_duration, i.e. it does not include the time needed to transfer the data to the client? Or is it more like \timing in that respect? Cheers, Viktor Craig Ringer wrote: > EXPLAIN ANALYZE measures the time taken to execute the query. It d

Re: [GENERAL] 10/13 SFPUG meeting, "The Mighty GUCS," video now available

Hi, I second the request for the files refered to in the video -- particularly postgresql.conf.simple and dependencies. Cheers, Viktor Christophe Pettus wrote: > The video archive for the 10/13 SFPUG meeting, "The Mighty GUCS: A > guide to the essential PostgreSQL settings you need to know," is

Re: [GENERAL] Can't find documentation for ~=~ operator

Tom Lane wrote: > Viktor Rosenfeld writes: > > I can't find the documentation of the ~=~ operator anywhere on the > > PostgreSQL homepage. > > Which version's documentation are you reading? It's gone as of 8.4. I realize that, but I have to use 8.3 right

[GENERAL] Can't find documentation for ~=~ operator

Hi, I can't find the documentation of the ~=~ operator anywhere on the PostgreSQL homepage. I'm quite certain that I saw it there a while ago, though. FYI, I use ~=~ for equality checks, so I can use the same index for equality and regexp pattern matching on a varchar field. The index is built

[GENERAL] How can I find out the space used on disk for a table/index

Hi, I want to judge the performance gain of additional indexes against the space they consume on disk. Currently I do a VACUUM FULL ANALYZE and then take the space used by the base directory and do some calculations. This is time consuming, error-prone and unreliable. Is there a quick w

Re: [GENERAL] Graphical representation of query plans

Much better than explain-analyze.info Many thanks! Am 23.06.2009 um 11:25 schrieb Dragan Sahpaski: Viktor Rosenfeld wrote: Thank you, this does indeed look very nice. I would still be interested in alternatives, though. Specifically, I want to be able to quickly see the cost of query

Re: [GENERAL] Graphical representation of query plans

Thank you, this does indeed look very nice. I would still be interested in alternatives, though. Specifically, I want to be able to quickly see the cost of query subplans á la http://explain-analyze.inf o. A tool that outputs a dot file or something which I can further edit would also work

[GENERAL] Graphical representation of query plans

Hi everybody, is there a (stand-alone, command line) tool that converts the output of EXPLAIN ANALYZE into a tree-like representation of the plan? Cheers, Viktor -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [GENERAL] UPDATE runs slow in a transaction

. what means "run under transaction"? you did exactly statements in psql console: begin; explain analyze select ... commit? regards Pavel Stehule 2008/7/16 Viktor Rosenfeld <[EMAIL PROTECTED]>: Hi Pavel, thanks for the advice on how to uncorrelate the query. I must admit I d

Re: [GENERAL] UPDATE runs slow in a transaction

: hello second query: why without transactions are not called triggers constraint _FK_struct_2_collection and constraint _FK_struct_2_text? Regards Pavel Stehule 2008/7/16 Viktor Rosenfeld <[EMAIL PROTECTED]>: Hi Pavel, thanks for the advice on how to uncorrelate the query. I must a

Re: [GENERAL] UPDATE runs slow in a transaction

t.id = tmp.id) regards Pavel Stehule 2008/7/16 Viktor Rosenfeld <[EMAIL PROTECTED]>: Hi Tom, Postgres is indeed selecting a bad plan. Turns out that the index I created to speed up the UPDATE isn't used inside a transaction block. Here's the plan for "UPDATE _struct SET left

Re: [GENERAL] UPDATE runs slow in a transaction

anks, Viktor Am 14.07.2008 um 20:52 schrieb Tom Lane: Viktor Rosenfeld <[EMAIL PROTECTED]> writes: the script below runs very fast when executed alone. But when I call it from within a transaction block it's so slow that I have to abort it after a while. Specifically the second-to-last

[GENERAL] UPDATE runs slow in a transaction

Hi, the script below runs very fast when executed alone. But when I call it from within a transaction block it's so slow that I have to abort it after a while. Specifically the second-to-last UPDATE seems to take forever within a transaction while it completes in about 3 seconds outside

Re: [GENERAL] temporarily deactivate an index

Hi Scott, Am 07.06.2008 um 16:53 schrieb Scott Marlowe: I'm experimenting with different indexes to speed up my queries and I was wondering if it is possible to temporarily deactivate an index, so it won't be considered in the evaluation of query plans. The reason is that dropping and then

[GENERAL] temporarily deactivate an index

Hi, I'm experimenting with different indexes to speed up my queries and I was wondering if it is possible to temporarily deactivate an index, so it won't be considered in the evaluation of query plans. The reason is that dropping and then rebuilding an index takes up time which I would r

[GENERAL] Questions about index usage

Hi, I have a query with the following joins: annotations2.attribute = 'pos' AND annotations2.value = 'KOUS' AND annotations4.span = 'man' AND annotations6.span = 'sich' AND annotations2.text_ref = annotations4.text_ref AND annotations2.right = anno

Re: [GENERAL] Re: passing a temporary table with more than one column to a stored procedure

Valentine On Apr 28, 11:52 pm, [EMAIL PROTECTED] (Viktor Rosenfeld) wrote: Hi Jon, Am 28.04.2008 um 19:23 schrieb Roberts, Jon: What does the signature of graphovertokens look like? Three parmaters and it doesn't return a setof? This is my problem. The return type is setof something

Re: [GENERAL] passing a temporary table with more than one column to a stored procedure

Hi Jon, Am 28.04.2008 um 19:23 schrieb Roberts, Jon: What does the signature of graphovertokens look like? Three parmaters and it doesn't return a setof? This is my problem. The return type is setof something (doesn't really matter), but I don't know what to put into the argument list.

[GENERAL] passing a temporary table with more than one column to a stored procedure

Hi, is there a way to pass a temporary result table with more than column to a stored procedure? Specifically, I would like to run a variation of this query: SELECT * FROM graphOverTokens( ( SELECT 1 AS min, 10 AS max, 2 AS text_ref