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;

Hmm, I was able to get an index-only scan and get all pages to be visible by doing the VACUUM in the same session. Running VACUUM from PgAdmin GUI didn't have any effect.

This did the trick:
VACUUM "SERIES".min1_009;
EXPLAIN ANALYZE SELECT count(*)
FROM "SERIES".min1_009;

But I'm still confused about this, do I need to log in on the database with the login I use and manually do the vacuum? Because auto-vacuum doesn't update the visibility maps for that user or session or something? Should auto-vacuum do this?

> > 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.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general