> There was an
> attempt to fix it (for example so that it could try to do a
> seqscan+sort
> instead of indexscan), but it stalled.
Actually I read that, but it's complicated... it involves planning and a lot of
other stuff I don't even know about...
My "solution" I guess would be easier (but,
> I've found it easier to select everything into another
> table, truncate
> the original table, then insert the rows as:
that takes 50 seconds of pure sorting and 8GB of ram to sort; my method doesn't
require more memory than the size of the heap table, and no sorting, since the
index is alread
Hi,
I have a table with 15M rows. Table is around 5GB on disk.
Clustering the table takes 5 minutes.
A seq scan takes 20 seconds.
I guess clustering is done using a seq scan on the index and then fetching the
proper rows in the heap.
If that's the case, fetching random rows on disk is the caus
> When adding a new field in the existing table, i
> want to add the filed in a particular position.
I'm afraid the only way would be re-writing the whole table (pseudo sql):
BEGIN;
create table newtable as select field1, 'newfield default value', field2 from
old_table;
create_all_indexes on ne
I've never received any reply to this post; as I said, I think I have a dump
that recreates the problem.
--- Ven 14/8/09, Scara Maccai ha scritto:
> Da: Scara Maccai
> Oggetto: Re: [GENERAL] totally different plan when using partitions
> A: "Tom Lane"
> Cc: "
> are null.
> But there's also a number saying how many columns there are
Uh, thank you.
Looking for t_natts I think I've found the discussion:
http://archives.free.net.ph/message/20090521.144512.3ffe4801.en.html
Thank you again.
--
Sent via pgsql-general mailing list (pgsql-general@postg
> I have a problem: I need put the "table name" as
> a parameter in a function!
I think you have to use dynamic statements:
http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.o
Hi all,
in the docs I read:
"Adding a column with a non-null default [...] will require the entire table to
be rewritten"
I guess that that can't be read as "you can always assume that adding a
nullable column to a table won't rewrite the whole table".
I'm asking because since the page layout
Query:
set enable_mergejoin=off;set enable_hashjoin=off;
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_test as data on
data.ne_id=cell_bsc.nome1
left outer
> 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
> rows?
Oh my... didn't notice it!!!
> What PG version is this
That was 8.4 beta1; now tried on
select version()
"PostgreSQL 8.4.0 on sparc-s
> 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 q
> > -> 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 inde
> 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'
> 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
> 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 precisio
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
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 ar
Anyone??? This looks like a bug to me... or is there an explanation?
--- Mer 12/8/09, Scara Maccai ha scritto:
> Da: Scara Maccai
> Oggetto: [GENERAL] totally different plan when using partitions
> A: "pgsql-general"
> Data: Mercoledì 12 agosto 2009, 13:05
>
query using partitions explicitly ("1"):
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 teststsce
> Is there a Python driver that uses the same protocol form
> of addBatch in Java?
I'll answer my own question: the method to be used should be
cursor.executemany()
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.pos
Hi all,
using Java I'm able to get a 1 inserts/sec on our server using batch
updates (using preparedStatement.addBatch()).
Using Python I can't get past 2000 inserts/sec, which is roughly the same
performance of Java without addBatch.
Is there a Python driver that uses the same protocol f
> But that would be a different query -- there's no
> restrictions on the
> t values in this one.
There is a restriction on the t values:
select * from idtable left outer join testinsert on id=ne_id where groupname='a
group name' and time between $a_date and $another_date
> Have you tried som
I have a problem with the method that PG uses to access my data.
Data into testinsert is inserted every 15 minutes.
ne_id varies from 1 to 2.
CREATE TABLE testinsert
(
ne_id integer NOT NULL,
t timestamp without time zone NOT NULL,
v integer[],
CONSTRAINT testinsert_pk PRIMARY KEY
> I am looking for something similar
> to:
> http://www.arcanadev.com/adtempus/ but without
> the MS SQL server dependency.
http://www.pgadmin.org/docs/1.4/pgagent.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.p
Hi,
I query is using Index scan instead of Bitmap Index Scan because it "thinks"
that the number of rows returned will be low (12). In fact, the number of rows
returned is 120.
This happens because there are some WHERE conditions that check cross-tables
values (something like taba.col1 < tabb
"The Solaris ZFS file system is safe with disk write-cache enabled because it
issues its own disk cache flush commands"
Could someone explain?
would that mean that I checking the disk cache with "format -e" on Solaris is
not needed if I use ZFS
Thanks
--
Sent via pgsql-general maili
Hi,
is there any chance to get the "Planner Cost Constants" right enough to get a
"good" estimate in seconds of how long a query is supposed to run?
The "rowcount" estimates are always good (there is no skew data at all in the
db, values are pretty much "plain" distributed)
--
Sent via p
Ops, found it:
http://www.postgresql.org/docs/8.4/static/view-pg-stats.html
("correlation" column)
I guess it would be worth pointing it out in the planner docs...
Scara Maccai wrote:
> Hi,
>
> does the planner know how "scattered" rows are? If yes:
Hi,
does the planner know how "scattered" rows are? If yes: where is this info
stored?
I'm asking because it looks to me that not only the number of rows, but also
how data is on disk (more or less sequential) is used to determine what type of
index scan (bitmap/regular) has to be used...
bu
you
--- Lun 18/5/09, Sam Mason ha scritto:
> Da: Sam Mason
> Oggetto: Re: [GENERAL] referring to calculated column in sub select
> A: pgsql-general@postgresql.org
> Data: Lunedì 18 maggio 2009, 19:10
> On Mon, May 18, 2009 at 06:49:30AM
> -0700, Scara Maccai wrote:
> > why c
Hi,
why column "acoltest" is not found by the subselect in this select:
SELECT
acol + 100 as acoltest,
(select max(t) from mytab where anothercol=acoltest) as col2
FROM mytab2
group by somet
???
Only columns belonging to a table can be used in a subselect??? What about
"calculated"
I've altready asked this some months ago, but I've never seen any answers:
why do multidimensional arrays have to have matching extents for each
dimension?
Is there any way this limit can be removed, even using a custom datatype?
__
Do You Yahoo!
What? Hot standby won't make it in 8.4?
That's a shame...
- Messaggio originale -
> Da: Fujii Masao
> A: pie...@hogranch.com
> Cc: pgsql-general@postgresql.org
> Inviato: Martedì 24 febbraio 2009, 20:47:05
> Oggetto: Re: [GENERAL] speaking of 8.4...
>
> Hi,
>
> On Tue, Feb 24, 2009 at
Thank you: that's exactly what I needed.
> I think you want to use a full outer join with slightly unusual
> bracketing:
>
> SELECT t.id, COALESCE(a.t,b.t) AS t, a.a, b.b
> FROM tabid t LEFT JOIN (
> taba a FULL OUTER JOIN tabb b ON (a.id,a.t) = (b.id,b.t))
> ON t.id = COALESCE(a.
Hi all,
I want to get data from these tables:
TABID
integer id,
name varchar
example values:
1 'id1'
2 'id2'
[...]
TABA
integer id,
timestamp t,
integer a
example values:
1 '2009-02-13 00:00:00' 10
1 '2009-02-13 02:00:00' 19
TABB
integer id,
timestamp t,
integer b
example values:
1 '2009-02-13 0
,value)::timemax_t), 3) from test where
id = 10 AND quarter between '2008-12-01 00:00:00' and '2008-12-01 10:00:00' ;
- Messaggio originale -
> Da: Scara Maccai
> A: pgsql-general@postgresql.org
> Inviato: Venerdì 30 gennaio 2009, 9:35:53
> Oggetto: complex cu
Anyone?
- Messaggio inoltrato -
> Da: Scara Maccai
> A: pgsql-general
> Inviato: Venerdì 30 gennaio 2009, 13:59:09
> Oggetto: [GENERAL] arrays and block size
>
> Hi,
>
> I need to store a lot of int8 columns (2000-2500) in a table.
>
> I was thinking a
Paolo Saudin wrote:
> I use a master table with a "fulldate" field and filled with sequential dates
> to
> fill gaps when meteo data is missing.
I'm sorry, I still don't get it: how can you be sure that postgresql won't call
perl_sliding_mean with not-ordered timestamps-data? I don't mean only
> Paolo Saudin wrote:
> For that purpose, a sliding mean calculation I use the following
>
> CREATE TABLE tbl_ayas
> (
> fulldate timestamp without time zone NOT NULL,
> id_1 real, -- temperature
> id_2 real, -- pressure
> ..
> CONSTRAINT tbl_ayas_pkey PRIMARY KEY (fulldate)
> )
Hi,
I need to store a lot of int8 columns (2000-2500) in a table.
I was thinking about using int8[], and I would like to know:
1) is there a max size for arrays? I guess I could have 1 GB "worth" of values,
but I would like a confirmation
2) there won't be any updates, only inserts and selects;
Gregory Stark wrote:
> From what I read of your
> description you want to produce one record per input record.
Exactly.
> 8.4 Will have OLAP Window functions which can implement things like
> moving averages.
Using 8.3: could I do it caching all the values "somewhere" in a custom
aggregation
Hi all,
I have a table like:
value int,
quarter timestamp
I need an aggregate function that gives back the maximum "value" using
this algorithm:
AVG of the first hour (first 4 quarters) (AVG0)
same as above, but 1 quarter later (AVG1)
same as above, but n quarters later (AVGn)
result: th
> Yeah, this is a bug: it's effectively double-counting the
> selectivity of the index clause. The error isn't enormously critical,
> since the join size estimate is right; but it could perhaps lead to
> choosing a plain indexscan when a bitmap scan would be better. I've
> applied a patch.
Tha
Really I'm worried about reducing storage space and network overhead
- therefore a nicely compressed chunk of binary would be perfect for
the 500 values - wouldn't it?
For storage space you might want to look at ZFS with compression on in
case you are using FreeBSD or Solaris.
That would s
Tom Lane wrote:
Scara Maccai <[EMAIL PROTECTED]> writes:
-> Index Scan using id_idx on tab1 (cost=0.00..8.27 rows=1
width=4) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (id = 10)
-> Index Scan using out_id_idx on tab_outer (cos
Query is:
"explain analyze select sum(A), count(*) from tab1 left outer join
tab_outer on id=out_id where id=10;"
output:
Aggregate (cost=31.91..31.92 rows=1 width=4) (actual
time=14.185..14.185 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..17.11 rows=2959 width=4)
(actual ti
I don't understand: is my question not clear, stupid, or you guys just
don't like me? ;)
Original Message
Subject:[Fwd: Re: [GENERAL] return MAX and when it happened]
Date: Fri, 21 Nov 2008 08:48:44 -0600
From: Scara Maccai <[EMAIL PROTECTED]>
To
> If you watch the speed, you'll see that the insert
> speed is the
> same, but the scan speed is worse (from 32k to 200).
As I said, I don't know a lot about these things.
But I would like someone to comment on this (so that maybe I will know
something!):
1) I thought the poor insert perfo
> But unfortunately the query speed is not good at all
> because most
> queries are for a specific client (and sensor) in a given
> time
> range...
Maybe I'm wrong, I don't know a lot of these things; but defining the index as
(timestamp, clientid, sensor) instead of (clientid, sensor, timest
Since you always need the timestamp in your selects, have you tried indexing
only the timestamp field?
Your selects would be slower, but since client and sensor don't have that many
distinct values compared to the number of rows you are inserting maybe the
difference in selects would not be that
Can someone answer me? Or do I have to ask this in the hackers list?
I don't get from the docs: do I have to call
get_call_result_type(fcinfo, NULL, &tupdesc)
every time?
I mean: the only example I've found about returning Composite Types
talks about returning sets as well (34.9.10. Returni
Sam Mason wrote:
On Wed, Nov 19, 2008 at 05:06:14PM -0600, Scara Maccai wrote:
I think I wrote it, but there's something I don't get from the docs: do
I have to call
get_call_result_type(fcinfo, NULL, &tupdesc)
I've always tried to stay away from C level extension
Sam Mason wrote:
The custom aggregate sounds the
most elegant, it's just annoying that it's so much fiddling to get it
all working to start with
Thanks.
I think I wrote it, but there's something I don't get from the docs: do
I have to call
get_call_result_type(fcinfo, NULL, &tupdesc)
every
Sam Mason wrote:
Do you really want the SUM of num1 and num2, because that makes it more
fiddly and would be where having MAX accept a record/tuple would be
best. If you don't, maybe something like:
SELECT DISTINCT ON (date_trunc('day', mydate))
date_trunc('day', mydate), num, num1+num2
Sam Mason wrote:
Why not just do:
SELECT * FROM mytab
ORDER BY num, mydate
LIMIT 1;
If you're trying to do more complicated things, DISTINCT ON may be more
useful.
Well you guys are right; the problem is that the query is actually more
complex, I tried to simplify it for the questio
Hi all,
suppose I have a table like:
CREATE TABLE mytab
(
num integer,
mydate timestamp
);
and I want to find MAX(num) and the "mydate" where it first happened.
I guess I could use
select * from mytab where num = (select MAX(num) from mytab) order by
mydate limit 1;
but that would sc
> FWIW, I believe that count(*) is pretty slow in Oracle too.
Well COUNT was only an example. I think (but I'm not sure AT ALL) that
SELECT A FROM myTAB where A <1
only uses the index (if there's an index defined for A) in Oracle.
But mine was just curiosity... which I think you and Sam an
Hi,
if I got it right the reason some aggregates (such as COUNT) using only index
columns are "slow" on postgresql is that it uses MVCC, so it has to read the
data as well as the index. It makes sense to me, but I don't understand is how
other databases (such as Oracle) do it.
Can someone expla
> You are the first person to ever ask, and searching for ' -' is pretty
> basic. If it is a problem, I think some other symbol should be used.
using opera it doesn't work... and with Firefox you still get a lot of
not-wanted matches...
Of course, this is not a "problem", I was just thinking th
Hi,
I was looking at the TODO:
http://www.postgresql.org/docs/faqs.TODO.html
"A hyphen, "-", marks changes that will appear in the upcoming 8.4 release."
Well, making a search for the "-" sign is complicated... it's obviously
used for a lot of other things... could you use another character?
> create or replace function sum_elements(anyarray)
> returns anyelement as $$
> select sum($1[i])
>from generate_series(array_lower($1,1),
>array_upper($1,1)) g(i);
> $$ language sql immutable;
Thank you! Anyway what I was really asking was a "$" (or w
> create or replace function sum_elements(anyarray)
> returns anyelement as $$
> select sum($1[i])
>from generate_series(array_lower($1,1),
>array_upper($1,1)) g(i);
> $$ language sql immutable;
Thank you! Anyway what I was really asking was a "$" (or w
Hi,
is there a syntax that sums the values of an array?
That is, having an array like
{1,3,8},{5,6,7}
something like
select arr[1:2][2:3]
gives
{1,3},{6,7}
; what I'd like is:
select arr[1$2][2$3]
gives:
17 (that is, 1+3 + 6+7)
If there is no such operator, would it be complicated add
> > >> You should have. The system enforces (or tries to) that a view can't be
> > >> part of an inheritance hierarchy, but you seem to have managed to find a
> > >> sequence of operations that avoids those checks. Turning a table into a
> > >> view with a manual CREATE RULE operation has always
> Exactly, because inheritance/constraint exclusion wont work with views.
Ok, so there should be something written in the docs about it...
From:
"the information about a view in the PostgreSQL
system catalogs is exactly the same as it is for a table. So for the
parser, there is absolutely no
is something
I can work on later.
At least I would like to see it working, since there is nothing in the docs
that says it shouldn't be working...
4) I am not able to rewrite my queries.
- Messaggio originale -
Da: Marko Kreen <[EMAIL PROTECTED]>
A: Scara Maccai <[EMAIL
I can't get views to participate in the hierarchy...
create table outings1 as select * from outings_root limit 0;
alter table outings1 inherit outings_root;
SELECT *
FROM dblink('host=myhost dbname=tacche port=5433 user=postgres
password=postgres'::text,
'SELECT * from outings1'::te
Alvaro Herrera wrote:
> I think you can do pretty much the same thing with
PL/Proxy; see
>
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy
Mmmh, I actually looked into that but I thought it
only worked with user functions...
am I wrong?
What I'd like to have is an almost-transparent
h
Hi,
I started thinking that using dblink I could "easily" get some kind of read
only multi-server partitioning, if only VIEWs could be declared with
"INHERITS"...
That way I think I could
1) add as many views as the number of DBs as
CREATE VIEW mytable_part_n AS
INHERITS mytable
to every D
Put the test-values into a temporary table, analyse it and then join
against it.
Ok, I didn't think of it.
Can't say about indexes without knowing more about your
usage pattern.
What do you mean?
---(end of broadcast)---
TIP 2: Don't 'kil
Hi,
from a table with 100,000,000 rows I have to sum some records using a
query like:
select sum(field1) from mytab where
(time = 1 and id = 3)
or
(time = 3 and id = 1)
or
(time = 2 and id = 5)
[...]
The "or clauses" can be even 10,000,000...
Which would be the best method to access data?
Hi,
I have a custom "database" (a very fast select/slow insert db) written
in c/c++.
I can access it with mysql writing a "Custom Engine". That is pretty
cool because now all my "custom db" tables can be joined with tables in
mysql's format.
I only need read access to my custom table format db
72 matches
Mail list logo
