Chris Travers, 23.08.2012 05:22:
The fact that this allows you to create essentially derived values
from groups of re-used columns is itself remarkable and can be used
to implement path traversal etc. which is not directly supported in
PostgreSQL in the sense that it is in Oracle or DB2.
What
Mike Christensen, 23.08.2012 02:41:
Oh, also if anyone knows of a way to export an Access database to
Postgres, that might be helpful. I don't have a copy of Access.
If you have a Windows box, you can try SQL Workbench/J. Even though it is a
Java application it can connect to an Access
I have a table with 40 million rows and haven't had any performance issues yet.
Are there any rules of thumb as to when a table starts getting too big?
For example, maybe if the index size is 6x the amount of ram, if the table is
10% of total disk space, etc?
--
Sent via pgsql-general
On Wed, Aug 22, 2012 at 05:56:27PM -0700, Jeff Janes wrote:
It's wide-ish, too, 98 columns.
How many of the columns are NULL for any given row? Or perhaps
better, what is the distribution of values for any given column? For
a given column, is there some magic value (NULL, 0, 1, -1, ,
I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc? -- My rule here is that a
Hello ppl,
I try to make query and see how many ids have more then one row.
few records is:
ids | val | some
a | 1 | x
a | 1 | v
b | 1 | x
b | 2 | c
I focus on ids and val with:
SELECT ids, val FROM table WHERE ids = 'a' GROUP BY ids, val HAVING
COUNT(ids) 1;
and result
Am 23.08.2012 09:52, schrieb Condor:
Hello ppl,
I try to make query and see how many ids have more then one row.
few records is:
ids | val | some
a | 1 | x
a | 1 | v
b | 1 | x
b | 2 | c
I focus on ids and val with:
SELECT ids, val FROM table WHERE ids = 'a'
On , Frank Lanitz wrote:
Am 23.08.2012 09:52, schrieb Condor:
Hello ppl,
I try to make query and see how many ids have more then one row.
few records is:
ids | val | some
a | 1 | x
a | 1 | v
b | 1 | x
b | 2 | c
I focus on ids and val with:
SELECT ids, val FROM table WHERE
Am 23.08.2012 10:45, schrieb Condor:
On , Frank Lanitz wrote:
Am 23.08.2012 09:52, schrieb Condor:
Hello ppl,
I try to make query and see how many ids have more then one row.
few records is:
ids | val | some
a | 1 | x
a | 1 | v
b | 1 | x
b | 2 | c
I focus on ids and
On Wed, Aug 22, 2012 at 4:06 PM, Nick nboutel...@gmail.com wrote:
I have a table with 40 million rows and haven't had any performance issues
yet.
Are there any rules of thumb as to when a table starts getting too big?
For example, maybe if the index size is 6x the amount of ram, if the
Hello List,
I have a simple table of keys and values which periodically
receives updated values. It's desirable to keep older values
but, most of the time, we query only for the latest value of a
particular key.
CREATE TABLE kv
( k bytea NOT NULL,
at timestamptz NOT NULL,
realm bytea
Le mercredi 22 août 2012 à 13:30 -0400, Sébastien Lorion a écrit :
Vincent, I would appreciate that you stop assuming things based on
zero information about what I am doing. I understand that you are
trying to be helpful, but I can assure you that going bare-metal only
does not make any sense
On Thu, Aug 23, 2012 at 7:39 AM, Vincent Veyron vv.li...@wanadoo.fr wrote:
Lest we ridicule ourselves publicly, I suggest we leave the discussion
at that and wish you luck in your endeavor.
If anyone has an answer to his question, I'd appreciate hearing it,
despite any faux pas that the OP has
On 2012-08-22, Nick nboutel...@gmail.com wrote:
I have a table with 40 million rows and haven't had any performance issues
yet.
Are there any rules of thumb as to when a table starts getting too big?
when you need to run a query that needs to fetch too many rows.
For example, maybe if the
In response to Martin French martin.fre...@romaxtech.com:
I have a table with 40 million rows and haven't had any performance
issues yet.
Are there any rules of thumb as to when a table starts getting too big?
For example, maybe if the index size is 6x the amount of ram, if the
Vincent,
The original question can be summed up by how is general performance of
PostgreSQL on Amazon IOPS. I fail to understand why that would require me
to explain the specifics of my application and/or my market. The only one
asking for that information is you, while others have provided
Thanks for all the replies !
The real problem has nothing to do with names. I just used that as a vehicle
for articulating the problem.
The view approach for queries is workable, at least for queries. Thanks for
the input on that and the idea to replicate the various aliases in the view!
On Wed, Aug 22, 2012 at 6:06 PM, Nick nboutel...@gmail.com wrote:
I have a table with 40 million rows and haven't had any performance issues
yet.
Are there any rules of thumb as to when a table starts getting too big?
For example, maybe if the index size is 6x the amount of ram, if the
On 08/23/2012 07:39 PM, Vincent Veyron wrote:
Le mercredi 22 août 2012 à 13:30 -0400, Sébastien Lorion a écrit :
Vincent, I would appreciate that you stop assuming things based on
zero information about what I am doing. I understand that you are
trying to be helpful, but I can assure you that
On Thu, Aug 23, 2012 at 6:46 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Aug 22, 2012 at 6:06 PM, Nick nboutel...@gmail.com wrote:
I have a table with 40 million rows and haven't had any performance issues
yet.
Are there any rules of thumb as to when a table starts getting too big?
Chris Travers chris.trav...@gmail.com writes:
On Thu, Aug 23, 2012 at 6:46 AM, Merlin Moncure mmonc...@gmail.com wrote:
Partitioning doesn't reduce index size -- it makes total index size
*bigger* since you have to duplicate higher nodes in the index --
unless you can exploit the table
Updateable views. This is great. I didn't know about these. Absolutely
delicious !
I found a great example here...
http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/
The problem of user updating 1 alias remains, but I have no problem bouncing
them if they attempt that.
On 08/23/2012 09:32 PM, Gauthier, Dave wrote:
The view approach for queries is workable, at least for queries. Thanks for
the input on that and the idea to replicate the various aliases in the view!
The key issue with all your approaches is whether the client can ever
`UPDATE` the view. If
Hi, i just formatted my machine and installed fresh win7 x64. Also
installed VS2012 since i do .net developement. In backend i use postgresql
so downloaded latest postgresql 9.1.5 installation. But, i am not able to
install. Upon executing file postgresql-9.1.5-1-windows.exe it asks for
UAC and i
Jason Dusek jason.du...@gmail.com writes:
I have a simple table of keys and values which periodically
receives updated values. It's desirable to keep older values
but, most of the time, we query only for the latest value of a
particular key.
CREATE TABLE kv
( k bytea NOT NULL,
at
On Aug 22, 2012, at 9:38 PM, Craig Ringer wrote:
On 08/23/2012 10:32 AM, Michael Sacket wrote:
The good news is I now have the proper constraints in place and the app and
it's 130 tables are working with PostgreSQL in less than a day.
Wow, that's cool, especially without SQL changes.
I have a table with 40 million rows and haven't had any performance issues yet. Are there any rules of thumb as to when a table starts getting too big? For example, maybe if the index size is 6x the amount of ram, if the table is 10% of total disk space, etc?My rule here is
On Tue, Aug 21, 2012 at 11:27 PM, Wells Oliver wellsoli...@gmail.com wrote:
We have a lot of tables which store numeric data. These tables all use the
numeric type, where the values are 95% integer values. We used numeric
because it eliminated the need for casting during division to yield a
On Wed, Aug 22, 2012 at 4:12 PM, Alan Hodgson ahodg...@simkin.ca wrote:
On Wednesday, August 22, 2012 04:10:01 PM Andrew Hannon wrote:
Just looking into High IO instances for a DB deployment. In order to get
past 1TB, we are looking at RAID-0. I have heard
(http://hackerne.ws/item?id=4266119)
Hello,
Can we get a screenshot? Also you can check for any partial installation
logs in your %TEMP% as install-postgresql.log or
bitrock_installer_.log. Check the %TEMP% of the Administrator as well
(If you dont see any logs in the %TEMP% of the logged in user)
On Thu, Aug 23, 2012 at 8:01
On 08/23/12 6:49 AM, Craig Ringer wrote:
In this case, what he's doing is seeking generalized performance
measurements. I don't think details were particularly necessary until
it got pulled off-track.
42
performance measurements without a very narrow definition of
'performance' are
Hello all!
I'm on Ubuntu 11.10, using 9.1.4. There was a new package for 9.1.5, and I just
finished my upgrade. I make use of the uuid-ossp and tablefunc extensions. What
is the expected upgrade procedure for these extensions? A drop followed by a
create? Or is there something else less
On 23 Srpen 2012, 18:19, François Beausoleil wrote:
Hello all!
I'm on Ubuntu 11.10, using 9.1.4. There was a new package for 9.1.5, and I
just finished my upgrade. I make use of the uuid-ossp and tablefunc
extensions. What is the expected upgrade procedure for these extensions? A
drop
Tomas Vondra t...@fuzzy.cz writes:
On 23 Srpen 2012, 18:19, François Beausoleil wrote:
I'm on Ubuntu 11.10, using 9.1.4. There was a new package for 9.1.5, and I
just finished my upgrade. I make use of the uuid-ossp and tablefunc
extensions. What is the expected upgrade procedure for these
One more thing i forgot to add
The console - help - about window reads
Tcl for Windows
Tcl 8.5.9
Tk 8.5.9
So i searched and tried downloading and running the latest Tcl available
from http://www.activestate.com/activetcl
And what do you know, the same thing as in the screenshot running that tcl
With \timing set on, I run an update statement and it reports
Time: 0.524 ms
Is that really 0.524 ms? As in 524 nanoseconds?
Also, is this wallclock time or some sort of indication of how much cpu it took?
Thanks for any answers !
I think both kind of tests (general and app specific) are complementary and
useful in their own way. At a minimum, if the general ones fail, why go to
the expenses of doing the specific ones ? Setting up a meaningful
application test can take a lot of time and it can be hard to pinpoint
exactly
On Aug 23, 2012, at 11:13 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
With \timing set on, I run an update statement and it reports
Time: 0.524 ms
Is that really 0.524 ms? As in 524 nanoseconds?
0.524ms = 524000ns
Perhaps you meant microseconds?
0.524ms = 524us
If all
On 08/23/12 11:24 AM, Sébastien Lorion wrote:
I think both kind of tests (general and app specific) are
complementary and useful in their own way. At a minimum, if the
general ones fail, why go to the expenses of doing the specific ones ?
Setting up a meaningful application test can take a lot
On 08/23/12 11:13 AM, Gauthier, Dave wrote:
Time: 0.524 ms
Is that really 0.524 ms? As in 524 nanoseconds?
0.524 MILLIseconds. as in 524 microseconds. microseconds is commonly
abbreviated us.
afaik, its elapsed time, not CPU time.
--
john r pierceN 37, W
Hi All,
We are currently running PostgreSQL 8.4 on Windows server 2003 and are planning
to move the instance to Ubuntu 10.4 - yay!. At the same time we will also
upgrade to 9.1. One nice features that we leverage from the windows
configuration is the ability for windows clients to use AD SSO
On Tue, Aug 21, 2012 at 2:56 AM, Craig Ringer ring...@ringerc.id.au wrote:
On 08/21/2012 03:01 PM, Martijn van Oosterhout wrote:
Well, Postgres in principle supports arrays of records, so I've
wondered if a relationship join could stuff all the objects in a single
field of the response using
On Wed, Aug 22, 2012 at 10:22 PM, Chris Travers chris.trav...@gmail.com wrote:
I have now been working with table inheritance for a while and after
starting to grapple with many of the use cases it has have become
increasingly impressed with this feature. I also think that some of
the
HI Scott.
Thanks a lot for the feedback.
I ended up setting the client time zone to GMT on my connections, and that
has fixed the problem for us.
It's actually an awesome solution, we can now expect all timestamps to be
returned in a consistent fashion.
Thanks for prodding me on that and sending
On Wed, Aug 22, 2012 at 5:00 AM, hubert depesz lubaczewski
dep...@depesz.com wrote:
On Tue, Aug 21, 2012 at 05:29:14PM -0400, Michael Clark wrote:
For example, if I insert like so:
INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');
I get the following when I select:
Hi:
I want to create a string from the first 3 elements of a csv (for example).
The csv is longer than 3 elements. Example...
aaa,bbb,ccc,ddd,eee,fff,ggg
I want the string aaa,bbb,ccc.
Tried splitting this to an array (precursor to appending elements 1,2,3), but
failed to be able to
Thomas,
On Wed, Aug 22, 2012 at 12:25 PM, Thomas Munro mu...@ip9.org wrote:
I noticed that 'avg' works on 'interval', but 'stddev' and 'variance' don't:
I don't know why, but you could convert 'interval' into something else
where all the functions work:
CREATE OR REPLACE FUNCTION
On 08/23/2012 02:30 PM, Gauthier, Dave wrote:
I want to create a string from the first 3 elements of a csv (for
example). The csv is longer than 3 elements. Example...
aaa,bbb,ccc,ddd,eee,fff,ggg
I want the string aaa,bbb,ccc.
select array_to_string
(
(
Hi,
On 24 August 2012 07:39, Christopher Swingley cswin...@gmail.com wrote:
I don't know why, but you could convert 'interval' into something else
where all the functions work:
CREATE OR REPLACE FUNCTION interval_to_seconds(interval)
RETURNS double precision AS $$
SELECT (extract(days
On Wed, Aug 22, 2012 at 4:06 PM, Nick nboutel...@gmail.com wrote:
I have a table with 40 million rows and haven't had any performance issues
yet.
Are there any rules of thumb as to when a table starts getting too big?
No. Assuming you decided it were too big, what could you do about it?
If
On Thu, Aug 23, 2012 at 6:37 AM, Bill Moran wmo...@potentialtech.com wrote:
In response to Martin French martin.fre...@romaxtech.com:
I have a table with 40 million rows and haven't had any performance
issues yet.
Are there any rules of thumb as to when a table starts getting too big?
Hi all
I've noticed that FETCH doesn't seem to be supported in subqueries or in
CTEs.
Is there a specific reason for that, beyond nobody's needed it and
implemented it? I'm not complaining at all, merely curious.
A toy example:
DECLARE somecursor CURSOR FOR SELECT
On Thu, Aug 23, 2012 at 12:36 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Aug 22, 2012 at 10:22 PM, Chris Travers chris.trav...@gmail.com
wrote:
I have now been working with table inheritance for a while and after
starting to grapple with many of the use cases it has have become
On Fri, 2012-08-24 at 09:35 +0800, Craig Ringer wrote:
Hi all
I've noticed that FETCH doesn't seem to be supported in subqueries or in
CTEs.
Is there a specific reason for that, beyond nobody's needed it and
implemented it? I'm not complaining at all, merely curious.
1. Cursors have
One other thing that seems worth mentioning is that as soon as you
jump from relational to object-relational modelling is that the latter
is more rich and hence more complex than the former. Because
object-relational modelling is a much expanded semantic superset of
relational modelling, the
On 08/23/2012 10:19 PM, Gauthier, Dave wrote:
Updateable views. This is great. I didn't know about these. Absolutely
delicious !
I found a great example here...
http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/
The problem of user updating 1 alias remains, but I have no
Hi,
On 24 August 2012 11:44, Chris Travers chris.trav...@gmail.com wrote:
One thing I have found looking through Oracle and DB2 docs is that
their table inheritance seems to have all the same problems as ours
and their solutions to these problems seem rather broken from a
pure relational
Dear friends,
Anyone has experienced extreme slowness running PostgreSQL 9.1.4 on
virtualized CentOS 5.8 on VMware ESXi 5.0 (with all Paravirtualized
drivers)?
By extreme slowness, consider a query that brings one record from a
table with 5000 records (using the PK as criteria) takes
On Thu, 23 Aug 2012 17:56:37 -0700 Jeff Janes jeff.ja...@gmail.com wrote:
On Thu, Aug 23, 2012 at 6:37 AM, Bill Moran wmo...@potentialtech.com wrote:
In response to Martin French martin.fre...@romaxtech.com:
I have a table with 40 million rows and haven't had any performance
issues
On 08/24/2012 02:30 AM, John R Pierce wrote:
On 08/23/12 11:13 AM, Gauthier, Dave wrote:
Time: 0.524 ms
Is that really 0.524 ms? As in 524 nanoseconds?
0.524 MILLIseconds. as in 524 microseconds. microseconds is commonly
abbreviated us.
They should be µs ; (micro µ seconds s). Sadly,
Hi,
On 23 August 2012 23:37, Bill Moran wmo...@potentialtech.com wrote:
And the advice I have along those lines is to establish now what
constitutes unacceptable performance, and put some sort of monitoring
and tracking in place to know what your performance degradation looks
like and
On 08/23/12 7:31 PM, Craig Ringer wrote:
0.524 MILLIseconds. as in 524 microseconds. microseconds is commonly
abbreviated us.
They should be µs ; (micro µ seconds s). Sadly, many setups still
can't type anything outside 7-bit ASCII even in 2012
yeah, I know I could enter the alt+xyz
On 08/24/2012 10:18 AM, Edson Richter wrote:
Dear friends,
Anyone has experienced extreme slowness running PostgreSQL 9.1.4 on
virtualized CentOS 5.8 on VMware ESXi 5.0 (with all Paravirtualized
drivers)?
https://wiki.postgresql.org/wiki/Slow_Query_Questions
Examine `EXPLAIN ANALYZE` for
I will be setting up an instance in the coming days and post the results
here.
While reading on the subject, I found this interesting discussion on
YCombinator:
http://news.ycombinator.com/item?id=4264754
Sébastien
On Thu, Aug 23, 2012 at 2:41 PM, John R Pierce pie...@hogranch.com wrote:
On
Hello
2012/8/24 Craig Ringer ring...@ringerc.id.au:
Hi all
I've noticed that FETCH doesn't seem to be supported in subqueries or in
CTEs.
Is there a specific reason for that, beyond nobody's needed it and
implemented it? I'm not complaining at all, merely curious.
A toy example:
65 matches
Mail list logo
