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
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
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 already
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, of
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 m_li...@yahoo.it ha scritto:
Da: Scara Maccai m_li...@yahoo.it
Oggetto: Re: [GENERAL] totally different plan when using partitions
A: Tom Lane t
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
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
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
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.org)
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
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
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
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
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
-
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
- 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
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
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
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:
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
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
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
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
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:
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 mailing
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
s...@samason.me.uk ha scritto:
Da: Sam Mason s...@samason.me.uk
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 column acoltest
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...
but
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: where is this
info stored?
--
Sent via
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
What? Hot standby won't make it in 8.4?
That's a shame...
- Messaggio originale -
Da: Fujii Masao masao.fu...@gmail.com
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,
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
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 =
-
Da: Scara Maccai m_li...@yahoo.it
A: pgsql-general@postgresql.org
Inviato: Venerdì 30 gennaio 2009, 9:35:53
Oggetto: complex custom aggregate function
Hi all,
I have a table like:
value int,
quarter timestamp
I need an aggregate function that gives back the maximum value using
Anyone?
- Messaggio inoltrato -
Da: Scara Maccai m_li...@yahoo.it
A: pgsql-general pgsql-general@postgresql.org
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
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)
) WITH
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 in
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: the
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,
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;
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.
Thank
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
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
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 (cost=0.00..8.83
rows=1
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: postgresql
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, timestamp)
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
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
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.
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 extensions so far! How
many
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
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
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,
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
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
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
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 that
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 whatever
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 whatever
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
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 been a kluge,
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
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 PROTECTED]
Cc: pgsql-general
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
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
SELECT using dblink on remote
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
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?
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
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
