Anyone??? This looks like a bug to me... or is there an explanation?
--- Mer 12/8/09, Scara Maccai m_li...@yahoo.it ha scritto:
Da: Scara Maccai m_li...@yahoo.it
Oggetto: [GENERAL] totally different plan when using partitions
A: pgsql-general pgsql-general@postgresql.org
Data: Mercoledì 12
Scara Maccai wrote:
same query, but using postgresql's partition pruning (2):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73 as data on
data.ne_id=cell_bsc.nome1
left outer join
On Wed, 12 Aug 2009, sam mulube wrote:
is my interpreting of buffers_clean = 0 correct?
Yes.
If so, why would the bgwriter not be writing out any buffers?
The purpose of the cleaner is to prepare buffers that we expect will be
needed for allocations in the near future. Let's do a
Thank you for your reply.
This makes partitions unusable for me... hope someone explains why this
happens... this still looks like a bug to me...
BTW the problem arises when adding the second left outer join: when using
only 1 partitioned table (that is, only 1 left outer join) the 2 plans are
Scara Maccai wrote:
Thank you for your reply. This makes partitions unusable for me...
hope someone explains why this happens... this still looks like a bug
to me... BTW the problem arises when adding the second left outer
join: when using only 1 partitioned table (that is, only 1 left
outer
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
I'd
According to the documentation, you can pass multiple parameters into an
aggregate function, but it only stores one value.
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
My function should take 2 values, the quantity and the unit, determine
I'm still looking into it, but it seems the difference in the 2 plans is due to
the fact that when using partitions, the planner adds the time it would take to
index-scan the empty root table.
But that table will never contain any data...
Is there any chance to have the partitioning mechanism
Is there a better way?
I think you could use a User Data Type.
Then pass that as parameter to your aggregate function.
That is: you would pass
(4, 'meter')
(400, 'mm')
(100, 'cm')
to your aggregate function.
Each one is a user datatype:
CREATE TYPE mytype AS (
v double
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
Running postgres.exe gives the error:
Execution of PostgreSQL by a user with administrative permissions is
not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the
Hello,
I have reported this yesterday via WWW as bug 4979, but I can't see it
in the -bugs archive. Has it been lost or are the bug reports being
moderated...?
Anyway. Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3.
radek=# \d kandydaci
Table public.kandydaci
PG Subscriber wrote:
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
Running postgres.exe gives the error:
Execution of PostgreSQL by a user with administrative permissions is
not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security
On 13 Aug 2009, at 12:51, Sim Zacks wrote:
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
4 meter
400 mm
100 cm
I want to sum it all, my function decides to use meter (based on the
requirements) and should return 4.00104 (or something
On Thu, Aug 13, 2009 at 13:21, PG Subscribermypg...@gmail.com wrote:
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4.
Running postgres.exe gives the error:
Execution of PostgreSQL by a user with administrative permissions is
not permitted.
The server must be started under an
On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinskira...@pld-linux.org wrote:
Hello,
I have reported this yesterday via WWW as bug 4979, but I can't see it
in the -bugs archive. Has it been lost or are the bug reports being
moderated...?
Anyway. Pg 8.4.0 from yum.postgresql.org running
It's probably easiest to decide on an internal unit to use in your
aggregate and only convert it to the desired unit once you're done
summing them. I'd probably convert all measurements to mm in the
function and summarise those.
That could work in some cases, however in our case it would not
That could work in some cases, however in our case it would
not produce
desirable results.
Well I don't think you got Alban's suggestion right...
What he was trying to say was:
- use a regular (not aggregated) function to convert all measures to mm
- use the normal SUM() to sum those value
-
Scara Maccai wrote:
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_0610_1 as data on
data.ne_id=cell_bsc.nome1
left outer join teststscell13_0610_1 as data1 on
Sim Zacks wrote:
According to the documentation, you can pass multiple parameters into an
aggregate function, but it only stores one value.
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
Have you seen Martijn van Oosterhout's tagged types?
Huh, clearly not the same query (you're using the partition
directly in
the first query) ... Doing two changes at once is not
helping your
case.
Sorry, I don't understand... of course I used the partition directly in the
first query... it's the difference between the two... what I don't
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Thanks,
Phil.
--
Philip Rhoades
GPO Box 3411
Sydney NSW 2001
Australia
E-mail: p...@pricom.com.au
--
Sent via pgsql-general
Scara Maccai wrote:
I'm still looking into it, but it seems the difference in the 2 plans is due to the fact
that when using partitions, the planner adds the time it would take to index-scan the
empty root table.
But that table will never contain any data...
Is there any chance to have the
- Index Scan using
teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9
rows=1 width=16) (actual time=0.006..0.006 rows=0
loops=285)
doesn't make any sense: that table will never have any
data.
I'd like to have a way to tell that to Postgresql...
It's one index probe and
Philip Rhoades wrote:
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Not sure about doing it with PostgreSQL but there is the Firefox Weave
Service which might do what I think you're
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for
Firefox Bookmarks - anyone know if this would be possible? how it
could be done?
Uh, I think it would consume more system resources.
--
Devrim GÜNDÜZ, RHCE
Command
Scara Maccai wrote:
Huh, clearly not the same query (you're using the partition directly
in the first query) ... Doing two changes at once is not helping
your case.
Sorry, I don't understand... of course I used the partition directly
in the first query... it's the difference between the
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the
Hi!
I must get informations about the tables.
For example:
Tnnn:
1. [Field Name, Type, Size, NotNull]
2. [Field Name, Type, Size, NotNull]
...
The test table is this:
CREATE TABLE testfields
(
fbigint bigint NOT NULL,
fbool boolean,
fchar character(100),
fcharv character varying(100),
On Wed, Aug 12, 2009 at 10:57:54PM +0200, Daniel Verite wrote:
It seems to me that there is something special with rows: in tables, the
values of columns may be null or not, but at the level of the row, there is
no information that would say: this row itself as an object is null.
Hum, there
What version are you using? Also,
please post the table
definitions (preferably in pg_dump -s format)
Table definition at the end of the msg.
Postgresql 8.4beta1
I'm not sure I agree with your assessment of the problem.
This is why I think that's the problem:
This is an explain of the
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
I'd
On Thu, Aug 13, 2009 at 11:02:37AM -0400, Dan Halbert wrote:
I am trying to make sense of geometric literal syntax in and out of
array syntax. I cannot figure out a general rule: sometimes single
quotes work, sometimes double quotes work, and inside and outside of
array literals the rules are
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the
On Thu, Aug 13, 2009 at 08:30:07AM -0700, Scott Bailey wrote:
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
Scara Maccai wrote:
set enable_mergejoin=off;
set enable_hashjoin=off
http://explain-analyze.info/query_plans/3817-query-plan-2525
Ah, good - that's useful.
As you can see, the 2 root partition roots (teststscell73 and teststscell13)
take
teststscell73: 3.90 * 30120 loops = 117468 cost
This just looks like PG missing a feature. plpgsql has much less user
and developer time spent on it, so I'd expect to find more strangeness
in darker corners like this.
this rule should be simply removed. It's not problem. The people long
time believe so row cannot be null ever. I don't
From Sam Mason s...@samason.me.uk:
The nicer syntax to distinguish things is to use:
TYPENAME 'literal'
Thanks! That is very helpful. I saw that syntax in one example I found on the
web, and incorrectly thought it was an alternate way of writing the function
call.
The point of all this was
Devrim,
On 2009-08-14 00:55, Devrim GÜNDÜZ wrote:
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for
Firefox Bookmarks - anyone know if this would be possible? how it
could be done?
Uh, I think it would consume more
Richard Huxton d...@archonet.com writes:
Scara Maccai wrote:
http://explain-analyze.info/query_plans/3817-query-plan-2525
Ah, good - that's useful.
Hmmm ... why is the inner Nested Loop estimated to produce 30120 rows,
when the cartesian product of its inputs would only be 285 * 14 = 3990
On Thu, Aug 13, 2009 at 12:31:29PM -0400, Dan Halbert wrote:
Perhaps I should have mentioned that initially.
In retrospect everything is easy!
SELECT ARRAY[1,2,1+2]::INT[];
works fine.
I'd not put a cast into that one. I can't see any performance reason
why it's bad, I think it's mainly
On Fri, 2009-08-14 at 02:36 +1000, Philip Rhoades wrote:
Devrim,
On 2009-08-14 00:55, Devrim GÜNDÜZ wrote:
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for
Firefox Bookmarks - anyone know if this would be
On Fri, 14 Aug 2009, Philip Rhoades wrote:
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Firefox uses SQLite to hold its bookmarks. It's certainly possible to
hack the code to use an
On Thu, Aug 13, 2009 at 05:20:22PM +0200, Durumdara wrote:
I must get informations about the tables.
For example:
Tnnn:
1. [Field Name, Type, Size, NotNull]
2. [Field Name, Type, Size, NotNull]
I'd recommend either using the standard defined information_schema[1] or
playing around with
Grzegorz Jaśkiewicz gryz...@gmail.com [2009-08-13 14:23]:
On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinskira...@pld-linux.org
wrote:
[...]
kandydaci_fk_id_rekordu FOREIGN KEY (id_rekordu) REFERENCES
rekordy(id) ON DELETE CASCADE
[...]
since you do LEFT JOIN, indeed you can get r.id
I am working on cleaning up a bloated database. I have been
reindexing etc. There appear to be a good number of never to almost
never used indexes. I am looking in pg_stat_user_indexes which yields
some questions.
Assuming that the reset stats on server is not turned on how old are
I have table like this:
CREATE TABLE messages {
recepients varchar[],
};
Want to select by content co array type field recepients:
SELECT * FROM messages where 'john' ANY (recepients);
If i want to create index on recepients field, is it enough to
CREATE INDEX messages_recepients_index
Hello,
I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
From all I can read I should have no problem with actual format of the
pgdump file (for actual dumping and restoring purposes) but I am having
problems with encoding (which I was fairly sure I would). I have
On Thu, Aug 13, 2009 at 04:02:14PM +0300, mito wrote:
I have table like this:
CREATE TABLE messages {
recepients varchar[],
};
Want to select by content co array type field recepients:
SELECT * FROM messages where 'john' ANY (recepients);
not sure if it went missing in the email,
a...@archie.netg.se writes:
I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
From all I can read I should have no problem with actual format of the
pgdump file (for actual dumping and restoring purposes) but I am having
problems with encoding (which I was fairly sure
Why don't you make it simple and just use row_number() from 8.4... It can be
simplified as:
select row_number() over(), i, p from prueba limit 5;
I know the use of WF. What surprised me, is the difference between
both versions in the same query. I'm trying to understand why happens
and not
Philip Rhoades wrote:
People,
It would be nice to be able to use PostgreSQL as the storage for Firefox
Bookmarks - anyone know if this would be possible? how it could be done?
Most likely it could be done, if you wanted to.
You will need to know how to write a firefox plugin, and how to
Hey folks,
I'm installing OTRS/ITSM (and yes, sending the same question to their
list) and it gives me this warning. I cannot find an equivalent
config parameter in Postgres.
Make sure your database accepts packages over 5 MB in size. A MySQL
database for example accepts packages up to 1 MB by
Alan McKay wrote:
Hey folks,
I'm installing OTRS/ITSM (and yes, sending the same question to their
list) and it gives me this warning. I cannot find an equivalent
config parameter in Postgres.
Make sure your database accepts packages over 5 MB in size. A MySQL
database for example
On Thu, 13 Aug 2009, Alan McKay wrote:
Make sure your database accepts packages over 5 MB in size. A MySQL
database for example accepts packages up to 1 MB by default. In this
case, the value for max_allowed_packet must be increased.
packages-packet for this to make sense; basically they're
On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote:
If your client app is coded correctly to handle large packets of data, it
should work up to the size limits documented at
http://www.postgresql.org/about/ , so you probably having nothing to worry
about here.
Is it worth having a
Hi. I'm trying to write a plperl function that returns a list of ids
that I want to use in a subquery.
The function call would look like:
select * from mlist( 168.4, 55.2, 0.1);
and would return a list of integers. I've written this function,
and it returns the right list of integers,
Sam Mason wrote:
Hum, there seem to be lots of different things happening here--lets try
and untangle them a bit. I would say that the following returns a null
value of type row (actually a pair of integers):
SELECT b
FROM (SELECT 1) a
LEFT JOIN (SELECT 1,2) b(b1,b2) ON
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Veritedan...@manitou-mail.org wrote:
In other discussions about similar issues I've said that the expression:
ROW(NULL,NULL) IS DISTINCT FROM NULL
should evaluate to FALSE. I still think this is correct and generally
useful behavior.
I see no
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
Sounds like you are trying to return directly the query.
You must do a loop with that query inside (cursor) and
use next
On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote:
On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote:
If your client app is coded correctly to handle large packets of data, it
should work up to the size limits documented at
http://www.postgresql.org/about/ , so you
On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote:
On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote:
Is it worth having a note about having enough memory floating around
for those limits to actually be hit in practice? There would be no
way of creating a row
On Thu, Aug 13, 2009 at 11:53:49PM +0100, Greg Stark wrote:
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Veritedan...@manitou-mail.org
wrote:
In other discussions about similar issues I've said that the expression:
ROW(NULL,NULL) IS DISTINCT FROM NULL
should evaluate to FALSE. I still
On Fri, Aug 14, 2009 at 12:33 AM, Sam Masons...@samason.me.uk wrote:
On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote:
On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote:
There would be no way of creating a row 1.6TB in size in one go
I was thinking of a
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christophe Pettus
On Aug 11, 2009, at 12:19 AM, Jan Verheyden wrote:
I was looking in what way it's possible to alert via mail when some
conditions are true in a
Greetings,
The video of the August 11, 2009 SFPUG talk, featuring David Fetter's
presentation on windowing and common table expressions, is now up:
http://thebuild.com/blog/2009/08/13/sfpug-windowing-and-common-table-expressions/
--
-- Christophe Pettus
x...@thebuild.com
--
Sent via
Radoslaw Zielinski wrote:
radek=# \d kandydaci
Table public.kandydaci
Column | Type | Modifiers
---+--+---
id_rekordu| bigint | not null
id_osoby | integer | not null
66 matches
Mail list logo
