Greg Stark <[EMAIL PROTECTED]> writes:
> I'm still puzzled why the times on these are so different when the latter
> returns fewer records and both are doing sequential scans:
My best guess is that it's simply the per-tuple overhead of cycling
tuples through the two plan nodes. When you have no a
In fact the number of records seems to be almost irrelevant. A sequential scan
takes almost exactly the same amount of time up until a critical region (for
me around 10 records) at which point it starts going up very quickly.
It's almost as if it's doing some disk i/o, but I'm watching vmstat
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg is correct. int8 is a pass-by-reference datatype
Just to keep the conversation on track. the evidence from this particular post
seems to indicate that my theory was wrong and the overhead for count(*) is
_not_ a big time sink. It seems to be at most 1
Neil Conway <[EMAIL PROTECTED]> writes:
> Interesting. Is there a reason why int8 is pass-by-reference?
Pass-by-value types have to fit into Datum.
On a 64-bit machine (ie, one where pointers are 64-bits anyway) it would
make sense to convert int8 (and float8 too) into pass-by-value types.
If the
On Mon, 2003-10-27 at 13:52, Tom Lane wrote:
> Greg is correct. int8 is a pass-by-reference datatype and so every
> aggregate state-transition function cycle requires at least one palloc
> (to return the function result).
Interesting. Is there a reason why int8 is pass-by-reference? (ISTM that
pa
Neil Conway <[EMAIL PROTECTED]> writes:
> On Mon, 2003-10-27 at 12:56, Greg Stark wrote:
>> Neil Conway <[EMAIL PROTECTED]> writes:
>>> Uh, what? Why would an int8 need to be "dynamically allocated
>>> repeatedly"?
>>
>> Perhaps I'm wrong, I'm extrapolating from a comment Tom Lane made that
>> pro
On Mon, 2003-10-27 at 12:56, Greg Stark wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > Uh, what? Why would an int8 need to be "dynamically allocated
> > repeatedly"?
>
> Perhaps I'm wrong, I'm extrapolating from a comment Tom Lane made that
> profiling showed that the bulk of the cost in cou
Neil Conway <[EMAIL PROTECTED]> writes:
> On Sun, 2003-10-26 at 22:49, Greg Stark wrote:
> > What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an
> > int8 to store its count so it's not limited to 4 billion records.
> > Unfortunately int8 is somewhat inefficient as it has to
On Mon, Oct 27, 2003 at 07:52:06AM -0500, Christopher Browne wrote:
> In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> > I was answering an earlier response that suggested that maybe the actual
> > counting took time so it would take quite a bit longer when there are
> > more
On Mon, Oct 27, 2003 at 11:12:37AM -0500, Vivek Khera wrote:
> > "DM" == Dror Matalon <[EMAIL PROTECTED]> writes:
>
> DM> effective_cache_size = 25520 -- freebsd forumla: vfs.hibufspace / 8192
>
> DM> 1. While it seems to work correctly, I'm unclear on why this number is
> DM> correct. 2552
> "DM" == Dror Matalon <[EMAIL PROTECTED]> writes:
DM> effective_cache_size = 25520 -- freebsd forumla: vfs.hibufspace / 8192
DM> 1. While it seems to work correctly, I'm unclear on why this number is
DM> correct. 25520*8 = 204160 or 200 Megs. On a machine with 2 Gigs it
DM> seems like the
On Sun, 2003-10-26 at 22:49, Greg Stark wrote:
> What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an
> int8 to store its count so it's not limited to 4 billion records.
> Unfortunately int8 is somewhat inefficient as it has to be dynamically
> allocated repeatedly.
Uh, what?
On Mon, 2003-10-27 at 10:15, Tarhon-Onu Victor wrote:
> select count(*) from items where channel <
> 5000; will never use any of the current indexes because none matches
> your WHERE clause (channel appears now only in multicolumn indexes).
No -- a multi-column index can be used to answer querie
On Sun, 26 Oct 2003, Dror Matalon wrote:
> Here's the structure of the items table
[snip]
> pubdate | timestamp with time zone |
> Indexes:
> "item_channel_link" btree (channel, link)
> "item_created" btree (dtstamp)
> "item_signature" btree (signature)
> "items_channel_arti
Christopher Browne <[EMAIL PROTECTED]> writes:
> In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> > I was answering an earlier response that suggested that maybe the actual
> > counting took time so it would take quite a bit longer when there are
> > more rows to count.
Tha
In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> I was answering an earlier response that suggested that maybe the actual
> counting took time so it would take quite a bit longer when there are
> more rows to count.
Well, if a "where clause" allows the system to use an index
On Mon, Oct 27, 2003 at 12:52:27PM +0530, Shridhar Daithankar wrote:
> Dror Matalon wrote:
>
> >On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
> >>Most of the time involves:
> >>
> >>a) Reading each page of the table, and
> >>b) Figuring out which records on those pages are st
Dror Matalon wrote:
On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
Most of the time involves:
a) Reading each page of the table, and
b) Figuring out which records on those pages are still "live."
The table has been VACUUM ANALYZED so that there are no "dead" records.
It's s
On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
> [EMAIL PROTECTED] (Dror Matalon) wrote:
> > On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> >> Dror Matalon <[EMAIL PROTECTED]> writes:
> >>
> >> > explain analyze select count(*) from items where channel < 5000;
>
[EMAIL PROTECTED] (Dror Matalon) wrote:
> On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
>> Dror Matalon <[EMAIL PROTECTED]> writes:
>>
>> > explain analyze select count(*) from items where channel < 5000;
>> > QUERY PLAN
>> > --
On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> Dror Matalon <[EMAIL PROTECTED]> writes:
>
> > explain analyze select count(*) from items where channel < 5000;
> > QUERY PLAN
> > -
Dror Matalon <[EMAIL PROTECTED]> writes:
> explain analyze select count(*) from items where channel < 5000;
> QUERY PLAN
> ---
22 matches
Mail list logo
