Re: Index only select count(*)

> On Oct 7, 2013, at 11:34, Toni Helenius <Toni(dot)Helenius(at)syncrontech(dot)com>
> wrote:
>
> > Hi,
> >
> > yes, I'm cheating by using GUI :) (PgAdmin)
>
> Please do not top-post.
>
> Analyze is an entirely different command than Explain analyze. Analyze
> updates the statistics of tables, while Explain analyze tells how those
> statistics affect the query plan.
>
> > Here:
> >
> > "Aggregate (cost=18240.50..18240.51 rows=1 width=0) (actual
> time=2911.117..2911.119 rows=1 loops=1)"
> > " -> Seq Scan on min1_009 (cost=0.00..18108.60 rows=52760 width=0)
> (actual time=5.390..2816.274 rows=52760 loops=1)"
> > "Total runtime: 2912.211 ms"
>
> That article you referenced mentions pg_class.relallvisible - what
> value does that have for your table?
> Is it possible that a relatively large amount of the data in that table
> is not visible to other sessions, or was that perhaps the case when you
> last (vacuum) analyzed the table?

Hmm, yes I didn't check these before.
SELECT pg_class.relname, pg_class.relallvisible, pg_class.relpages FROM pg_class where pg_class.relname = 'min1_009';
"min1_009";0;17581

So no pages are visible? How come? They should be? The schema I'm on has GRANT ALL ON SCHEMA "SERIES" TO public;

> What is the plan if you set enable_seqscan = off; in your session? Does
> that give any more insight?
>
> > -----Original Message-----
> > From: Alban Hertroys [mailto:haramrae(at)gmail(dot)com]
> > Sent: 7. lokakuuta 2013 12:31
> > To: Toni Helenius
> > Cc: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] Index only select count(*)
> >
> > On Oct 7, 2013, at 11:23, Toni Helenius
> <Toni(dot)Helenius(at)syncrontech(dot)com> wrote:
> >
> >> The output of analyze:
> >> "Aggregate (cost=18240.50..18240.51 rows=1 width=0)"
> >> " -> Seq Scan on min1_009 (cost=0.00..18108.60 rows=52760
> width=0)"
> >
> > That's the output of Explain, not of Explain Analyze. The latter has
> actual measurements to go with the estimated costs, which gives a lot
> more insight.
> >
> > Alban Hertroys
> > --
> > If you can't see the forest for the trees, cut the trees and you'll
> find there is no forest.
> >
>
> Alban Hertroys
> --
> If you can't see the forest for the trees, cut the trees and you'll
> find there is no forest.