Re: [GENERAL] select count(*);

> -Original Message- > From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Sent: Donnerstag, 11. Juni 2015 16:31 > To: Marc Mamin; 'Geoff Winkless'; Postgres General > Subject: Re: [GENERAL] select count(*); > > On 06/11/2015 07:17 AM, Marc Mami

Re: [GENERAL] select count(*);

On 11 June 2015 at 15:35, Yves Dorfsman wrote: > On 2015-06-11 08:20, Geoff Winkless wrote: > > On 11 June 2015 at 15:17, Marc Mamin > >wrote: > > > > >But COUNT(*) > > > > > >does have meaning - it means "the number of rows". > > > > which rows? :-)

Re: [GENERAL] select count(*);

On 06/11/2015 07:17 AM, Marc Mamin wrote: That's the point. * has no meaning without FROM But COUNT(*) does have meaning - it means "the number of rows". which rows? :-) To follow up on the post from Chris Mair: test=> select count(*), 'foo'; count | ?column? ---+-- 1

Re: [GENERAL] select count(*);

Hi Marc: On Thu, Jun 11, 2015 at 4:17 PM, Marc Mamin wrote: >>But COUNT(*) >>does have meaning - it means "the number of rows". > which rows? :-) Well, docs could use a little polish there, as the select page says """ Compatibility Of course, the SELECT statement is compatible with the SQL sta

Re: [GENERAL] select count(*);

On 2015-06-11 08:20, Geoff Winkless wrote: > On 11 June 2015 at 15:17, Marc Mamin >wrote: > > >But COUNT(*) > > > >does have meaning - it means "the number of rows". > > which rows? :-) > > > ​The number of rows in the query, as well you know :) B

Re: [GENERAL] select count(*);

Marc Mamin writes: >> The * might be a bit tricky, though, >> since 'select *;' doesn't work. > That's the point. * has no meaning without FROM PG regards "count(*)" as a weird spelling of "count()", ie, invoke an aggregate that takes no arguments. It really doesn't have anything to do with the

Re: [GENERAL] select count(*);

On 11 June 2015 at 15:17, Marc Mamin wrote: > >But COUNT(*) > > > >does have meaning - it means "the number of rows". > > which rows? :-) ​The number of rows in the query, as well you know :) The reason you can't SELECT *; is because there's no way of defining what "*" refers to in this instan

Re: [GENERAL] select count(*);

>>That's the point. * has no meaning without FROM >But COUNT(*) > >does have meaning - it means "the number of rows". which rows? :-) > It's not counting the number of columns in the row, so postgres doesn't need > to know what columns exist in the row to return a row count. >Geoff -- Sen

Re: [GENERAL] select count(*);

On 11 June 2015 at 15:05, Marc Mamin wrote: > That's the point. * has no meaning without FROM > ​ But COUNT(*) *​​does* have meaning - it means "the number of rows". ​ It's not counting the number of columns in the row, so postgres doesn't need to know what columns exist in the row to return a r

Re: [GENERAL] select count(*);

> > select *; > > -- > > ERROR: SELECT * with no tables specified is not valid > > > > select count(*); > > > > 1 > > > > Is this a must? and why 1? > > Hi, > > regarding the "why 1" part: > > I think that if we accept that > > chris=> select 'foo'; > ?column? > -

Re: [GENERAL] select count(*);

> select *; > -- > ERROR: SELECT * with no tables specified is not valid > > select count(*); > > 1 > > Is this a must? and why 1? Hi, regarding the "why 1" part: I think that if we accept that chris=> select 'foo'; ?column? -- foo (1 row) returns 1 row, t

[GENERAL] select count(*);

Hello, select *; -- ERROR: SELECT * with no tables specified is not valid select count(*); 1 Is this a must? and why 1? It may lead to uncatched issues by typos. e.g.: select count(*) FROMpg_stat_activity; FROMpg_stat_activity - 1 regards, Marc

Re: [GENERAL] SELECT count(*) differs from result in pgadmin

Tobias Larsen writes: > Oops, got it. Count(*) includes the rowcount of tables that inherit > from the table queried? I wasn't counting on that. To prevent inheriting tables from being scanned as well, run your query as; select count(*) from ONLY footable; > On Wed, May 8, 2013 at 4:44 PM,

Re: [GENERAL] SELECT count(*) differs from result in pgadmin

Oops, got it. Count(*) includes the rowcount of tables that inherit from the table queried? I wasn't counting on that. On Wed, May 8, 2013 at 4:44 PM, Tobias Larsen wrote: > This is a small, but weird problem. Completely regular table : > - requesting count in pgadmin shows 3124448 rows > - run

[GENERAL] SELECT count(*) differs from result in pgadmin

This is a small, but weird problem. Completely regular table : - requesting count in pgadmin shows 3124448 rows - running SELECT count(*) via the query tool returns 5997620 Why is there a difference? There's nothing remotely remarkable about the table. I've run a full database VACUUM just to be c

Re: [GENERAL] Select count with offset returns nothing.

Tim Uckun writes: > I am using a library which is emitting SQL like this SELECT COUNT(*) > FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres > this query returns nothing (not even zero as a result). Presumably it > returns some valid value on mysql and other databases. FWIW,

Re: [GENERAL] Select count with offset returns nothing.

Yea I figured it would need a subquery. I filed a ticket with the library. Hopefully they will fix it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Select count with offset returns nothing.

On Fri, Aug 5, 2011 at 4:51 AM, Tim Uckun wrote: > I am using a library which is emitting SQL like this  SELECT COUNT(*) > FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres > this query returns nothing (not even zero as a result). Presumably it > returns some valid value on mys

Re: [GENERAL] Select count with offset returns nothing.

Tim Uckun writes: > I am using a library which is emitting SQL like this SELECT COUNT(*) > FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres > this query returns nothing (not even zero as a result). Presumably it > returns some valid value on mysql and other databases. > > Ot

Re: [GENERAL] Select count with offset returns nothing.

My question is why would you put an offset in a query designed to return a row count without grouping and ordering? > Date: Fri, 5 Aug 2011 22:51:24 +1200 > Subject: [GENERAL] Select count with offset returns nothing. > From: timuc...@gmail.com > To: pgsql-general@postgresql.org >

Re: [GENERAL] Select count with offset returns nothing.

Hi, I think problem is in OFFSET 15 It means return rows after row 15... because of SELECT COUNT(*) FROM batches LIMIT 15 returns 1 row when you add OFFSET 15 - it returns nothing... because of there is no more than 15 rows... I am not sure u can do something else then to change library to rem

[GENERAL] Select count with offset returns nothing.

I am using a library which is emitting SQL like this SELECT COUNT(*) FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres this query returns nothing (not even zero as a result). Presumably it returns some valid value on mysql and other databases. Other than hacking the library is

Re: [GENERAL] Select count(*) /*from*/ table

Alexander Shulgin writes: > I understand that there's really not much point in running COUNT w/o > the FROM list, but maybe we should just disallow COUNT(*) with empty > FROM list? While I don't offhand see a use case for aggregates without FROM, it's a long way from there to asserting that there

[GENERAL] Select count(*) /*from*/ table

Hello, Today I've mistyped a SELECT (effectively omitting the FROM clause): $ SELECT COUNT(*) my_table; my_table -- 1 (1 row) Apparently, my_table was treated as an alias to the COUNT(*) expression. This has been discussed before, e.g. here: http://archives.postgresql.org/pgsql

Re: RES: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1)

, May 30, 2011 8:35:39 PM Subject: RES: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1) David, Thanks for your reply. I will probably use the strategy of a trigger driven counter, with temporal strategy devising current month totals and up to last month total as current month

RES: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1)

ssunto: RE: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1) Counting live data is inherently imprecise. There are supposedly some system tables that can give you rough numbers. You would be better off figuring out an alternative method to get the data you desire and stop c

Re: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1)

On 05/27/2011 12:41 PM, Carlos Sotto Maior (SIM) wrote: I have browsed catalog tables, digging for a real time Row.count but so far did not find any. See http://wiki.postgresql.org/wiki/Slow_Counting which shows you where the one system count estimate is at, as well as suggesting links to

Re: [GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1)

Counting live data is inherently imprecise. There are supposedly some system tables that can give you rough numbers. You would be better off figuring out an alternative method to get the data you desire and stop continually recounting all 5.7M records. A Trigger driven counter, for insert and d

[GENERAL] SELECT COUNT(*) execution time on large tables (v9.0.4-1)

Hi, My application has a frequent need to issue a select count(*) on tables. Some have a large row count. (The example below are from a 5.7 M row; Some are larger). Issuing either SELECT COUNT(*) or SELECT COUNT() yelds a sequential scan on table; I have browsed catalog tables, digging for a

Re: [GENERAL] select count(*)

i "discovered" it as a result of typo :) we usually select expressions without tables eg select 1+2 ; etc and the results are as expected, somehow i failed to stretch the analogy to count(*) which is mostly used over tables or table expression. thanks anyways. regds mallah. On Wed, Mar 9, 2011

Re: [GENERAL] select count(*)

In response to Rajesh Kumar Mallah : > Dear List , > > if we simply do select count(*) and not specify any table then it gives 1 > eg: > > bric=# SELECT count(*) from job ; > count > --- > 2380 > (1 row) > > bric=# SELECT count(*) job ; > job > - >1 > (1 row) > > > > bric=#

[GENERAL] select count(*)

Dear List , if we simply do select count(*) and not specify any table then it gives 1 eg: bric=# SELECT count(*) from job ; count --- 2380 (1 row) bric=# SELECT count(*) job ; job - 1 (1 row) bric=# SELECT count(*) ; count --- 1 (1 row) bric=# SELECT count(*) j

Re: [GENERAL] select count(*) status these days

Dennis Gearon wrote: Is there any architectural way to speed it up? I'd actually like to run it every 2-60 seconds to update a counter on a page for the marketing guy, (which for once, is me :- Could always maintain it with triggers. See http://wiki.postgresql.org/wiki/Slow_Counting for a d

Re: [GENERAL] select count(*) status these days

On 19/10/10 11:15, Dennis Gearon wrote: I'm about to launch an in house 'Aplpha' stage project. One of the marketing driven outputs on the page is filled by a: SELECT COUNT(*) WHERE date_field IS NOT NULL AND date_field> :todays_date; Last I heard, this kind of query is kind of slow

[GENERAL] select count(*) status these days

I'm about to launch an in house 'Aplpha' stage project. One of the marketing driven outputs on the page is filled by a: SELECT COUNT(*) WHERE date_field IS NOT NULL AND date_field > :todays_date; Last I heard, this kind of query is kind of slow on Postgres in particular, (Love postgres o

Re: [GENERAL] select count() out of memory

On 25 Okt, 17:36, [EMAIL PROTECTED] wrote: > > The design is based on access patterns, i.e. one partition represents a > group of data along a discrete axis, so the partitions are the perfect for > modeling that. Only the last partition will be used on normal cases. The > previous partitions only n

Re: [GENERAL] select count() out of memory

Adrian Klaver wrote: I'm thinking do a COPY to one large table. If the cost of indexing is relatively fixed as you indicated in your previous post then you reduce the indexing overhead to each COPY operation instead of each insert. No, what I meant whas that creating an index on a table wit

Re: [GENERAL] select count() out of memory

-- Original message -- From: Thomas Finneid <[EMAIL PROTECTED]> > > > Adrian Klaver wrote: > > I might be missing the point, but couldn't you do a Copy to a single table > > instead of multiple inserts and avoid the index overhead. > > Are you saying, have one

Re: [GENERAL] select count() out of memory

Adrian Klaver wrote: I might be missing the point, but couldn't you do a Copy to a single table instead of multiple inserts and avoid the index overhead. Are you saying, have one large table with indexes and do a COPY to it or are you saying a one small empty table and do a COPY to it? tho

Re: [GENERAL] select count() out of memory

On Friday 26 October 2007 8:56 am, [EMAIL PROTECTED] wrote: > > Serious engineering does not imply perfect engineering, I have analyzed it > and made my tradeoffs. What you are forgetting here is that you clearly > dont understand the enire solution, So I will try to explain it again. And > if you

Re: [GENERAL] select count() out of memory

> "Sam Mason" <[EMAIL PROTECTED]> writes: > >> On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote: >>> Gregory Stark wrote: >>> >Tom's point is that if you have 55k tables then just *finding* the >>> newest >>> >child table is fairly expensive. You're accessing a not >>> insignificant-s

Re: [GENERAL] select count() out of memory

On Fri, Oct 26, 2007 at 02:21:39PM +0100, Gregory Stark wrote: > "Sam Mason" <[EMAIL PROTECTED]> writes: > > I think the lookup that is being referred to is the fact that if you've > > got 55k (plus) files in a directory then the filesystem still has to > > perform a search in the directory to loca

Re: [GENERAL] select count() out of memory

"Sam Mason" <[EMAIL PROTECTED]> writes: > On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote: >> Gregory Stark wrote: >> >Tom's point is that if you have 55k tables then just *finding* the newest >> >child table is fairly expensive. You're accessing a not insignificant-sized >> >index

Re: [GENERAL] select count() out of memory

On Fri, Oct 26, 2007 at 08:25:09AM +0200, Thomas Finneid wrote: > Gregory Stark wrote: > >Tom's point is that if you have 55k tables then just *finding* the newest > >child table is fairly expensive. You're accessing a not insignificant-sized > >index and table of tables. And the situation is worse

Re: [GENERAL] select count() out of memory

On Fri, Oct 26, 2007 at 08:26:13AM +0200, Thomas Finneid wrote: > Scott Marlowe wrote: > >It may well be that one big table and partial indexes would do what > >you want. Did you explore partial indexes against one big table? > >That can be quite handy. > > Hmm, interresting, I suppose it could w

Re: [GENERAL] select count() out of memory

Jorge Godoy wrote: Em Thursday 25 October 2007 13:36:09 [EMAIL PROTECTED] escreveu: Regarding dumps and restore; the system will always be offline during those operations and it will be so for several days, because a new project might start at another location in the world, so the travelling th

Re: [GENERAL] select count() out of memory

Scott Marlowe wrote: It may well be that one big table and partial indexes would do what you want. Did you explore partial indexes against one big table? That can be quite handy. Hmm, interresting, I suppose it could work. Tanks for the suggestion, Ill keep it in mind. regards thomas -

Re: [GENERAL] select count() out of memory

Gregory Stark wrote: Tom's point is that if you have 55k tables then just *finding* the newest child table is fairly expensive. You're accessing a not insignificant-sized index and table of tables. And the situation is worse when you consider the number of columns all those tables have, all the

Re: [GENERAL] select count() out of memory

Em Thursday 25 October 2007 13:36:09 [EMAIL PROTECTED] escreveu: > > Regarding dumps and restore; the system will always be offline during > those operations and it will be so for several days, because a new project > might start at another location in the world, so the travelling there > takes tim

Re: [GENERAL] select count() out of memory

"Thomas Finneid" <[EMAIL PROTECTED]> writes: >> What you're >> effectively doing is replacing the upper levels of a big table's indexes >> with lookups in the system catalogs, which in point of fact is a >> terrible tradeoff from a performance standpoint. > > Only if you assume I use all data in

Re: [GENERAL] select count() out of memory

On 10/25/07, Thomas Finneid <[EMAIL PROTECTED]> wrote: > > Tom Lane wrote: > > > You are making a common beginner error, which is to suppose that N > > little tables are better than one big one. They are not. > > Well that depends on how you define better. For my purposes, it is better. > > > What

Re: [GENERAL] select count() out of memory

Tom Lane wrote: You are making a common beginner error, which is to suppose that N little tables are better than one big one. They are not. Well that depends on how you define better. For my purposes, it is better. What you're effectively doing is replacing the upper levels of a big tabl

Re: [GENERAL] select count() out of memory

On 10/25/07, Steve Crawford <[EMAIL PROTECTED]> wrote: > Alvaro Herrera wrote: > ... > > > > You can use CREATE TABLE LIKE, which copies the definition but does not > > set the inheritance. > > > > Well, sort of. > > Unless I'm using it incorrectly it only copies the basic column > definitions and,

Re: [GENERAL] select count() out of memory

Alvaro Herrera wrote: ... > > You can use CREATE TABLE LIKE, which copies the definition but does not > set the inheritance. > Well, sort of. Unless I'm using it incorrectly it only copies the basic column definitions and, as optionally specified, defaults and some of the constraints. Primary

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] writes: >> In other words, you really should have only one table; they aren't >> independent. What you need to do is dial down your ideas of how many >> partitions are reasonable to have. > Yes, but no. Each partition represents a chunk of information on a > discrete timeline. S

Re: [GENERAL] select count() out of memory

> this is my config > > checkpoint_segments = 96 > effective_cache_size = 128000 > shared_buffers = 43 > max_fsm_pages = 208000 > max_fsm_relations = 1 > > max_connections = 1000 > > autovacuum = off# enable autovacuum subprocess? > > fsync = on

Re: [GENERAL] select count() out of memory

On Oct 25, 2007, at 11:16 AM, Alvaro Herrera wrote: [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Thats good enough for me, thats exactly what I want. In that case, why use partitions at all? They are simple independent tables. For two reasons, - the data logically belongs together

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] writes: >>> In that case, why use partitions at all? They are simple independent >>> tables. > >> For two reasons, >> - the data logically belongs together. >> - because its more practical to create tables as childs of a parent >> table >> than as independent tables. >>- ch

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] wrote: > >> Thats good enough for me, thats exactly what I want. > > > > In that case, why use partitions at all? They are simple independent > > tables. > > For two reasons, > - the data logically belongs together. > - because its more practical to

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] wrote: >> > [EMAIL PROTECTED] wrote: >> >> Thats good enough for me, thats exactly what I want. >> > >> > In that case, why use partitions at all? They are simple independent >> > tables. >> >> For two reasons, >> - the data logically belongs together. >> - because its more pra

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] writes: >> In that case, why use partitions at all? They are simple independent >> tables. > For two reasons, > - the data logically belongs together. > - because its more practical to create tables as childs of a parent table > than as independent tables. >- changes to the

Re: [GENERAL] select count() out of memory

> Excellent, it sounds like you should be fine then. One thing to > note: if you want to get an "idea" of how many rows you have in your > partitions, you can run a SUM aggregate on reltuples in pg_class for > all of your partitions. The more recent the last ANALYZE for each > table, the more ac

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] wrote: >> Thats good enough for me, thats exactly what I want. > > In that case, why use partitions at all? They are simple independent > tables. For two reasons, - the data logically belongs together. - because its more practical to create tables as childs of a parent table t

Re: [GENERAL] select count() out of memory

Scott Marlowe escribió: > So, out of curiosity, I asked my Oracle DBA friend if she'd ever heard > of anyone having 60,000 or so partitions in a table, and she looked at > me like I had a third eye in my forehead and said in her sweet voice > "Well, that would certainly be an edge case". She soun

Re: [GENERAL] select count() out of memory

> So, out of curiosity, I asked my Oracle DBA friend if she'd ever heard > of anyone having 60,000 or so partitions in a table, and she looked at > me like I had a third eye in my forehead and said in her sweet voice > "Well, that would certainly be an edge case". She sounded like she > was worrie

Re: [GENERAL] select count() out of memory

> Are you selecting directly from the child table, or from the parent > table with constraint_exclusion turned on? the problem was when selecting from the parent table, but selecting from child tables are no problem. As stated in other replies, I only wanted to know how many rows where in the tabl

Re: [GENERAL] select count() out of memory

On Oct 25, 2007, at 10:36 AM, [EMAIL PROTECTED] wrote: The db worked fine until it reached perhaps 30-40 thousand partitions. It depends on how you have the partitions set up and how you're accessing them. Are all of these partitions under the same parent table? If so, then trying run a SE

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] wrote: > > It will work on a million partitions and more, provided you do > > operations on single partitions. > > Thats good enough for me, thats exactly what I want. In that case, why use partitions at all? They are simple independent tables. --

Re: [GENERAL] select count() out of memory

>> The db worked fine until it reached perhaps 30-40 thousand partitions. > > It depends on how you have the partitions set up and how you're > accessing them. Are all of these partitions under the same parent > table? If so, then trying run a SELECT COUNT(*) against the parent > table is simply

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] wrote: > It will work on a million partitions and more, provided you do > operations on single partitions. Thats good enough for me, thats exactly what I want. I just used the select count() on the root to get a feeling of how many rows it was in total. An then I thought that t

Re: [GENERAL] select count() out of memory

On 10/25/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi > > I am volume testing a db model that consists of a paritioned tables. The > db has been running for a week and a half now and has built up to contain > approx 55000 partition tables of 18000 rows each. The root table therefore > cont

Re: [GENERAL] select count() out of memory

On Oct 25, 2007, at 9:36 AM, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: are a dump of Postgres's current memory allocations and could be useful in showing if there's a memory leak causing this. The file is 20M, these are the last lines: (the first line c

Re: [GENERAL] select count() out of memory

On 10/25/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > >> [EMAIL PROTECTED] wrote: > >>> I did a test previously, where I created 1 million partitions (without > >>> data) and I checked the limits of pg, so I think it should be ok. > > > >> Clearl

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > The partitioning facility is designed for partition counts in the tens, > > or maybe hundreds at the most. > > Maybe, but it works even on 55000 partitions as long as the operations are > done against a partition and not

Re: [GENERAL] select count() out of memory

On 10/25/07, Tom Lane <[EMAIL PROTECTED]> wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > [EMAIL PROTECTED] wrote: > >> I did a test previously, where I created 1 million partitions (without > >> data) and I checked the limits of pg, so I think it should be ok. > > > Clearly it's not. > > Y

Re: [GENERAL] select count() out of memory

> Alvaro Herrera <[EMAIL PROTECTED]> writes: >> [EMAIL PROTECTED] wrote: >>> I did a test previously, where I created 1 million partitions (without >>> data) and I checked the limits of pg, so I think it should be ok. > >> Clearly it's not. > > You couldn't have tested it too much --- even planning

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] wrote: >> > [EMAIL PROTECTED] wrote: >> > >> >> > are a dump of Postgres's current memory allocations and could be >> >> useful in >> >> > showing if there's a memory leak causing this. >> >> >> >> The file is 20M, these are the last lines: (the first line continues >> >> unttil

Re: [GENERAL] select count() out of memory

Alvaro Herrera <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: >> I did a test previously, where I created 1 million partitions (without >> data) and I checked the limits of pg, so I think it should be ok. > Clearly it's not. You couldn't have tested it too much --- even planning a query o

Re: [GENERAL] select count() out of memory

> I have shown the entire configuration. if its not in the configuration > shown, I have changed its value. I meant to say "I haven't changed its value" thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] wrote: > > [EMAIL PROTECTED] wrote: > > > >> > are a dump of Postgres's current memory allocations and could be > >> useful in > >> > showing if there's a memory leak causing this. > >> > >> The file is 20M, these are the last lines: (the first line continues > >> unttill ff_26000

Re: [GENERAL] select count() out of memory

> [EMAIL PROTECTED] wrote: > >> > are a dump of Postgres's current memory allocations and could be >> useful in >> > showing if there's a memory leak causing this. >> >> The file is 20M, these are the last lines: (the first line continues >> unttill ff_26000) >> >> >> idx_attributes_g1_seq_1_ff_4_v

Re: [GENERAL] select count() out of memory

> God morgen > > Please display these memory settings from your postgresql.conf file > sort_mem > shared_buffers I have shown the entire configuration. if its not in the configuration shown, I have changed its value. I have used the configuration example provided by Sun regarding running postgres

Re: [GENERAL] select count() out of memory

[EMAIL PROTECTED] wrote: > > are a dump of Postgres's current memory allocations and could be useful in > > showing if there's a memory leak causing this. > > The file is 20M, these are the last lines: (the first line continues > unttill ff_26000) > > > idx_attributes_g1_seq_1_ff_4_value7: 1024

Re: [GENERAL] select count() out of memory

> <[EMAIL PROTECTED]> writes: > >> max_connections = 1000 > > Do you actually have anywhere near this number of processes? What is your > setting for work_mem? Keep in mind every process could use as much as > work_mem > and actually it's possible to use that much several times over. > > Also, what

Re: [GENERAL] select count() out of memory

25, 2007 7:07 AM Subject: Re: [GENERAL] select count() out of memory > Hi > > I have tried to answer to the best of my knowledge but its running on > Soalris 10, and I am not that familiar with solaris ( Go Linux!!! :) > > > any more memory. Either you have a very low memory ulimit

Re: [GENERAL] select count() out of memory

<[EMAIL PROTECTED]> writes: > max_connections = 1000 Do you actually have anywhere near this number of processes? What is your setting for work_mem? Keep in mind every process could use as much as work_mem and actually it's possible to use that much several times over. Also, what is your mainten

Re: [GENERAL] select count() out of memory

Hi I have tried to answer to the best of my knowledge but its running on Soalris 10, and I am not that familiar with solaris ( Go Linux!!! :) > any more memory. Either you have a very low memory ulimit (look at ulimit > -a > in the same session as Postgres) or your machine is really low on memory

Re: [GENERAL] select count() out of memory

<[EMAIL PROTECTED]> writes: > > ERROR: out of memory > DETAIL: Failed on request of size 130. > > Does anybody have any suggestion as to which parameter I should tune to > give it more memory to be able to perform queries on the root table? This indicates that malloc() failed which mea

[GENERAL] select count() out of memory

Hi I am volume testing a db model that consists of a paritioned tables. The db has been running for a week and a half now and has built up to contain approx 55000 partition tables of 18000 rows each. The root table therefore contains about 1 billion rows. When I try to do a "select count(*)" of th

Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity

"Logan Bowers" <[EMAIL PROTECTED]> writes: > In my case, the "raw" data is on the order of hundreds of gigabytes and > the increased write activity is a HUGE penalty. And you think the extra activity from repeated clog tests would not be a huge penalty? AFAICS this would only be likely to be a wi

Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity

Sent: Monday, August 01, 2005 7:09 PM To: Logan Bowers Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity "Logan Bowers" <[EMAIL PROTECTED]> writes: > I'm potentially having a strange performance problem. I have a BIG

Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity

"Logan Bowers" <[EMAIL PROTECTED]> writes: > I'm potentially having a strange performance problem. I have a BIG table: > ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will be slow) > and as I watch procinfo on my DB server I see a huge amount of write > activity. Thus, The only t

Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity

"Logan Bowers" <[EMAIL PROTECTED]> writes: > I'm potentially having a strange performance problem. I have a BIG > table: ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will > be slow) and as I watch procinfo on my DB server I see a huge amount of > write activity. Thus, > 1)

[GENERAL] SELECT count(*) Generating Lots of Write Activity

Hello,   I’m potentially having a strange performance problem.  I have a BIG table: ~100M, ~1KB rows.  I do a SELECT count(*) from it (I know it will be slow) and as I watch procinfo on my DB server I see a huge amount of write activity.  Thus,   1)   Why does this statement gener

Re: [GENERAL] select count(*) from pg_stat_activity in V8.0.0

Anony Mous wrote: Thanks, Richard. I've never seen this behaviour before in 7.4.3 and indeed it is the only connection to the back end at the time when the count is occurring. However, it would have had the connection for at least 30 seconds before requesting a count. Is there a better method of

Re: [GENERAL] select count(*) from pg_stat_activity in V8.0.0

g the number of connections to the backend? Thanks Peter -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: August 19, 2004 2:21 AM To: Anony Mous Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] select count(*) from pg_stat_activity in V8.0.0 Anony Mous wrote: > Comin

Re: [GENERAL] select count(*) from pg_stat_activity in V8.0.0

Richard Huxton <[EMAIL PROTECTED]> writes: > Anony Mous wrote: >> select count(*) from pg_stat_activity > Do you have multiple live connections? My understanding of > pg_stat_activity is that it can lag slightly behind the current state of > the system. If memory serves, it's up to 500 msec be

Re: [GENERAL] "select count(*) from contacts" is too slow!

Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] wrote: MySQL can tell you from it's index because it doesn't care if it gives you the right number or not. Under what circumstances would MySQL give the wrong number? It would give the wrong number under _e

Re: [GENERAL] "select count(*) from contacts" is too slow!

Ang Chin Han <[EMAIL PROTECTED]> writes: > Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs, > etc are autocommitted. That's sort of true, but not the whole story. Even autocommitted transactions can be pending for a significant amount of time. The reason it's accura

Re: [GENERAL] "select count(*) from contacts" is too slow!

A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] wrote: >> MySQL can tell you from it's index because it doesn't care if it gives you the >> right number or not. > > Under what circumstances would MySQL give the wrong number? It would give the wrong number under _every_ circumstance

  1   2   >