On Jan 15, 2008, at 3:03 PM, Ivan Sergio Borgonovo wrote:
On Tue, 15 Jan 2008 14:43:35 +0100
Alban Hertroys <[EMAIL PROTECTED]> wrote:
You need to scroll to the last row to find the size of the result
set, but after that it's pretty easy to return random rows by
scrolling to them (and marking
On Tue, 15 Jan 2008 14:43:35 +0100
Alban Hertroys <[EMAIL PROTECTED]> wrote:
> You need to scroll to the last row to find the size of the result
> set, but after that it's pretty easy to return random rows by
> scrolling to them (and marking them 'read' in some way to prevent
> accidentally
On Jan 9, 2008, at 8:07 PM, Scott Marlowe wrote:
I could see a use for an approximate count(*) with where clause, just
like I could see a use for the ability to retrieve random rows from a
table without using order by random() on it. And those are both
things that would require some form of hack
Chris Browne wrote:
> [EMAIL PROTECTED] (Zoltan Boszormenyi) writes:
>> SELECT COUNT(*)
> [Waving hands for a moment]
Would what Chris describes below be a good candidate for
a pgfoundry project that has functions that'll create the
triggers for you? (yeah, I might be volunteering, but would
undo
Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Chris Browne <[EMAIL PROTECTED]> writes:
>
> > There may be a further optimization to be had by doing a
> > per-statement trigger that counts the number of INSERTs/DELETEs done,
> > so that inserting 30 tuples (in the table being tracked) lea
In article <[EMAIL PROTECTED]>,
Chris Browne <[EMAIL PROTECTED]> writes:
> There may be a further optimization to be had by doing a
> per-statement trigger that counts the number of INSERTs/DELETEs done,
> so that inserting 30 tuples (in the table being tracked) leads to
> adding a single tuple wi
On Wed, Jan 09, 2008 at 03:28:04PM +0100, Ivan Sergio Borgonovo wrote:
> Let me consider an everyday use where count() looks as the most
> obvious solution: paging.
>
> I search trough a table and I need to know which is the last page.
There's an often overlooked solution to this. Let's say your
It would be an administrative nightmare unless you had very few where clauses
that you were tracking.
Instead of using a trigger, you could use Listen/Notify to call a daemon on the
server to run the procedure and then you have no insert/delete overhead.
Or you could call the function on a cro
[EMAIL PROTECTED] (Zoltan Boszormenyi) writes:
> which will be fast and depending on the initial value of COUNT(*)
> it will be very close to the exact figure. You can extend the example
> with more columns if you know your SELECT COUNT(*) ... WHERE
> conditions in advance but this way you have to
Ivan Sergio Borgonovo írta:
On Wed, 09 Jan 2008 20:29:39 +0100
Zoltan Boszormenyi <[EMAIL PROTECTED]> wrote:
The decision to use MVCC in PostgreSQL makes the point moot.
...
thanks.
In PostgreSQL, COUNT(*) responds closely at the same speed
regardless of other transactions. Whic
On Wed, 09 Jan 2008 20:29:39 +0100
Zoltan Boszormenyi <[EMAIL PROTECTED]> wrote:
> The decision to use MVCC in PostgreSQL makes the point moot.
...
thanks.
> In PostgreSQL, COUNT(*) responds closely at the same speed
> regardless of other transactions. Which way do you prefer?
Considering the
Ivan Sergio Borgonovo írta:
On Wed, 9 Jan 2008 13:04:39 +0100
"Harald Armin Massa" <[EMAIL PROTECTED]> wrote:
Ivan,
Please forgive my naiveness in this field but what does it mean an
"exact count" and what other DB means with "an exact count" and
how other DB deal with it?
On Wed, 9 Jan 2008 10:58:29 -0800
"Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> > OK... we are getting near to the point. I understand the trade-off
> > problem in storing into indexes id the row is still there.
> > Is there a way to get the count of the rows that *may be* there,
> If you analy
On Jan 9, 2008 12:58 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Wed, 9 Jan 2008 20:01:05 +0100
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
>
> > On Wed, 9 Jan 2008 10:30:45 -0600
> > "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> >
>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 9 Jan 2008 20:01:05 +0100
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> On Wed, 9 Jan 2008 10:30:45 -0600
> "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>
> > Now, everything's a tradeoff. If PostgreSQL had visibility
> > information in t
On Wed, 9 Jan 2008 10:30:45 -0600
"Scott Marlowe" <[EMAIL PROTECTED]> wrote:
> Now, everything's a tradeoff. If PostgreSQL had visibility
> information in the indexes, it would have to lock both the table and
> index for every write, thus slowing down all the other queries that
> are trying to ac
On Wed, Jan 09, 2008 at 11:03:59AM -0600, Scott Marlowe wrote:
>
> And if, for some god forsaken reason, you need to operate on that
> number, there's always "lock table"...
Yes. You could also store the data in ISAM :-P
> I feel dirty. :)
You should. Go wash your brain out with soap. LOCK T
On Jan 9, 2008 10:46 AM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> On Wed, Jan 09, 2008 at 05:21:24PM +0100, Ivan Sergio Borgonovo wrote:
> >
> > I got the impression that even counting with clauses on on indexed
> > columns means you'll have to check if columns are still there. That
> > seems t
On Wed, Jan 09, 2008 at 05:21:24PM +0100, Ivan Sergio Borgonovo wrote:
>
> I got the impression that even counting with clauses on on indexed
> columns means you'll have to check if columns are still there. That
> seems to imply that the extra cost make pg under perform compared to
> other DB even
On Jan 9, 2008 10:21 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> On Wed, 09 Jan 2008 16:33:54 +0200
> Sim Zacks <[EMAIL PROTECTED]> wrote:
>
> > Using count(*) is not bad design, though generally it makes sense
> > to use it with a where.
>
> I got the impression from others comments tha
On Jan 9, 2008 8:33 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
> Using count(*) is not bad design, though generally it makes sense to use it
> with
> a where.
>
> Saying using count(*) is bad design means that the only design that you can
> visualize is the specific one that you are using.
>
> There
On Wed, 09 Jan 2008 16:33:54 +0200
Sim Zacks <[EMAIL PROTECTED]> wrote:
> Using count(*) is not bad design, though generally it makes sense
> to use it with a where.
I got the impression from others comments that postgresql
under perform other DB even when a where clause on indexed column is
invo
Using count(*) is not bad design, though generally it makes sense to use it with
a where.
Saying using count(*) is bad design means that the only design that you can
visualize is the specific one that you are using.
There are tons of real world examples where you need count. That is why so ma
On Wed, 9 Jan 2008 13:04:39 +0100
"Harald Armin Massa" <[EMAIL PROTECTED]> wrote:
> Ivan,
> > Please forgive my naiveness in this field but what does it mean an
> > "exact count" and what other DB means with "an exact count" and
> > how other DB deal with it?
> PostgreSQL will give you an exact
Ivan,
> Please forgive my naiveness in this field but what does it mean an
> "exact count" and what other DB means with "an exact count" and how
> other DB deal with it?
PostgreSQL will give you an exact count of the contents of the
database as it is in the moment you begin your count. (i.e. the
On Wed, 9 Jan 2008 01:39:34 -0800
"Dann Corbit" <[EMAIL PROTECTED]> wrote:
> > On Wed, 09 Jan 2008 00:06:45 -0800
> > "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> > > Granted there are scenarios where others are FASTER (SELECT
> > > COUNT(*)) but I find that if you are doing those items, you
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Ivan Sergio Borgonovo
> Sent: Wednesday, January 09, 2008 1:30 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] count(*) and bad design was: Experiences wi
On Wed, 09 Jan 2008 00:06:45 -0800
"Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> Granted there are scenarios where others are FASTER (SELECT
> COUNT(*)) but I find that if you are doing those items, you
> normally have a weird design anyway.
> Sincerely,
Sincerely, would you make an example of
28 matches
Mail list logo
