Re: Index only select count(*)

> > > On Oct 7, 2013, at 11:34, Toni Helenius
> > > <Toni.Helenius@syncrontech.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?

Ok, from the GUI I checked FULL, FREEZE & ANALYZE. That combination doesn't seem to set the last vacuum date either. So
itwas wrong for me to use it like that. And auto-vacuum seems to update visibility maps as planned. My mistakes. Sorry
aboutthat. Everything seems to work. 

> > > 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@gmail.com]
> > > > Sent: 7. lokakuuta 2013 12:31
> > > > To: Toni Helenius
> > > > Cc: pgsql-general@postgresql.org
> > > > Subject: Re: [GENERAL] Index only select count(*)
> > > >
> > > > On Oct 7, 2013, at 11:23, Toni Helenius
> > > <Toni.Helenius@syncrontech.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@postgresql.org) To
> > make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general