On Tue, Mar 8, 2011 at 10:42 AM, Igor Neyman wrote:
> Indexes don't "maintain counts", indexes maintain pointers to the table
> records.
>
The whole point is that they don't, even if you can afford the costs.
What you need is "materialized view" storing aggregates.
> And it looks like you alrea
> -Original Message-
> From: Glenn Maynard [mailto:gl...@zewt.org]
> Sent: Monday, March 07, 2011 5:27 PM
> To: pgsql-general@postgresql.org
> Subject: Re: Why count(*) doest use index?
>
>
> An index on "events(user, event_time::date)" could optimize
> this, eg. effectively maintainin
On 7 Mar 2011, at 22:16, Glenn Maynard wrote:
> The stats system only helps for the most basic case--counting the number of
> rows in a table. In my experience that's not very common; most of the time
> it's counting total results from some more interesting query, eg. for
> pagination. In my
On Mon, Mar 7, 2011 at 5:58 PM, Merlin Moncure wrote:
> > SELECT COUNT(*), event_time::date FROM events
> > WHERE event_time::date >= '2011-01-01' AND event_time::date <
> '2011-02-01'
> > AND user=50
> > GROUP BY event_time::date;
>
> select count(*) from events
> where
> (user, event_ti
On Mon, Mar 7, 2011 at 4:26 PM, Glenn Maynard wrote:
> On Mon, Mar 7, 2011 at 4:35 PM, Merlin Moncure wrote:
>>
>> SELECT COUNT(*) FROM table WHERE expr;
>>
>> will use index (assuming expr is optimizable and is worth while to
>> optimize). Your case might be interesting for cache purposes if ex
On Mon, Mar 7, 2011 at 4:35 PM, Merlin Moncure wrote:
> SELECT COUNT(*) FROM table WHERE expr;
>
> will use index (assuming expr is optimizable and is worth while to
> optimize). Your case might be interesting for cache purposes if expr2
> is expensive, but has nothing to do with postgres index
2011/3/8 Merlin Moncure
> On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard wrote:
> > On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure
> wrote:
> >>
> >> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote:
> >> > That's often perfectly fine, with read-heavy, single-writer workloads.
> >> >
> >> >
On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard wrote:
> On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure wrote:
>>
>> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote:
>> > That's often perfectly fine, with read-heavy, single-writer workloads.
>> >
>> > I definitely wish there was a way to creat
On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure wrote:
> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote:
> > That's often perfectly fine, with read-heavy, single-writer workloads.
> >
> > I definitely wish there was a way to create indexes to track counters on
> > various types of queries, e
On Sun, Mar 6, 2011 at 3:41 AM, Martijn van Oosterhout
wrote:
> The other option is visibility data in the index. Doubles the size of
> your indexes though.
Also requires both table and index be locked while you update both so
you don't get race conditions. so has a real performance impact there
On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard wrote:
> On Sun, Mar 6, 2011 at 5:41 AM, Martijn van Oosterhout
> wrote:
>>
>> If it's really really important there are ways you can use trigger
>> tables and summary views to achieve the results you want. Except it's
>> expensive and when people are
On Sun, Mar 6, 2011 at 5:41 AM, Martijn van Oosterhout wrote:
> If it's really really important there are ways you can use trigger
> tables and summary views to achieve the results you want. Except it's
> expensive and when people are told that all of the sudden the count(*)
> performance isn't so
On 6 Mar 2011, at 9:03, Allan Kamau wrote:
> If this is true it seems that the primary key and perhaps other unique
> indexes do indeed contain exact details of the uniqueness of the
> persisted tuples of a given relation at any given time.
That is true within a single transaction, but indexes co
On Sun, Mar 06, 2011 at 11:03:23AM +0300, Allan Kamau wrote:
> I would assume the primary key or unique indexes are the cornerstone
> of each insertion and deletion.
> Or am I missing a crucial point.
The real issue is that you can have four programs all doing count(*)
and all getting different
On Sun, Mar 6, 2011 at 1:46 AM, John R Pierce wrote:
> On 03/05/11 2:05 PM, Allan Kamau wrote:
>>
>> Is it possible in theory to efficiently perform count the primary or
>> unique indices underlying data structures, regardless whether there is
>> a WHERE clause detailing filtration base on values
Other well known dbms's do have this possibility, because they place deleted
or updated records in a separate table or file (plz correct me if i'm
wrong). But this has other, greater performance disadvantages. The count(*)
problem is a bit of a publicity problem rather than a real performance
probl
On 03/05/11 2:05 PM, Allan Kamau wrote:
Is it possible in theory to efficiently perform count the primary or
unique indices underlying data structures, regardless whether there is
a WHERE clause detailing filtration base on values from such index or
not?
indexes are not exact, due to possibly c
On Sat, Mar 5, 2011 at 8:02 PM, Raymond O'Donnell wrote:
> On 03/03/2011 13:29, obamaba...@e1.ru wrote:
>>
>> I use pgsql 9.0.3 and I know that postgresql tries to use the fields in
>> indexes instead of the original table if it possible
>>
>> But when I run
>>
>> SELECT COUNT(id) FROM tab
>>
>> o
On 03/03/2011 13:29, obamaba...@e1.ru wrote:
I use pgsql 9.0.3 and I know that postgresql tries to use the fields in
indexes instead of the original table if it possible
But when I run
SELECT COUNT(id) FROM tab
or
SELECT COUNT(*) FROM tab
where there "id" is PRIMARY KEY and there are other i
On 03/03/2011 05:29 AM, obamaba...@e1.ru wrote:
I use pgsql 9.0.3 and I know that postgresql tries to use the fields in
indexes instead of the original table if it possible
But when I run
SELECT COUNT(id) FROM tab
or
SELECT COUNT(*) FROM tab
where there "id" is PRIMARY KEY and there are othe
I use pgsql 9.0.3 and I know that postgresql tries to use the fields
in indexes instead of the original table if it possible
But when I run
SELECT COUNT(id) FROM tab
or
SELECT COUNT(*) FROM tab
where
there "id" is PRIMARY KEY and there are other indexes there I get
execution plan that d
21 matches
Mail list logo
