Is there a way to create an index that would make this query be efficient
and not perform a sequential scan?

SELECT count(*) AS count,id FROM sometable GROUP BY id;

... I've considered creating a rule on this table which would put the
results of this into another table anytime it is updated, but I thought
there might be an easier way.

Thanks!

- Greg


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

I am not clear how to use a trigger for this, I will need to look into
that....

It is my understanding that Postgres does not have materialized views
though (which I believe would solve this problem nicely) - am I mistaken?...


- Greg




---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Saturday November 6 2004 7:34, Net Virtual Mailing Lists wrote:
Since you have no "WHERE" clause and you want to group by id, I believe
pgsql has to scan all id values. Those id values are only fully stored in
the table, so I don't think so.

Ed


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

But if you do build an index over "id" then pgsql would only have to do a
sequential scan on that index, which might be a lot faster if your table
contains a lot of other data, won't it?

Jerry

""Ed L."" <pgsql (AT) bluepolka (DOT) net> wrote

I'm not sure if it would improve performance at all, given the entire
table needs to be scanned anyway, but add a where clause "where id > 0"
should allow an index on id to be used.

Possibly a bit like speeding up "select max(id) from ..."

Someone who knows more about the internals of Postgis can prob comment on
the validity/idiocy of this suggestion :-)

Brent Wood

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sure. Try to create an index on id. Another way to improve this query
is to use HashAggregate (this is new in 7.4). Sometimes it is much
faster than group-by-using-index-scan. To enable HashAggregate
you might want to increase sort_mem.
--
Tatsuo Ishii

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

PostgreSQL doesn't provide pre-configured support for materialized views assuch, but using some PL/pgSQL and triggers, one can easily implement any kind of materialized view as seen fit for the specific intended purpose (Snapshot, Eager, Lazy, Very Lazy).

You may find an excellent tutorial on materialized views with PostgreSQL here: http://jonathangardner.net/PostgreSQ.../matviews.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Markus,

Thank you for your hint!

I spent the better part of last night working on this and finally was
able to get it to work the way I wanted. The short version: I am
continually amazed by the flexibility in Postgres, this isn't the sort of
thing I'd want to go back years from now and digest what I did but this
really has given me the best of both worlds: *very* quick query times
(4631ms down to 2 ms!) and when the data changes the trigger which does
the update is very quick, which beats my previous plan which involved
rerunning the query again and then caching the result for subsequent
queries....

Really this is great stuff! I simply cannot thank you (and all the other
folks on this list who have helped me) enough!

- Greg



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Sun, Nov 07, 2004 at 09:29:30 +0000,
Jerry III <jerryiii (AT) hotmail (DOT) com> wrote:
A full table index scan will be slower than a sequential scan; typically by
a lot. In the old days a sort step would have been needed and that would have
slowed things down. Now a method using hashing is available that will
work unless there is an extremely large number of unique values for "id".

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match