Can this be indexed?

[Net Virtual Mailing Lists]

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

[Ed L.]

On Saturday November 6 2004 7:34, Net Virtual Mailing Lists wrote:

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.

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

[Jerry III]

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."" <pg***@bluepolka.net> wrote in message
news:200411060930.30859.pg***@bluepolka.net...

On Saturday November 6 2004 7:34, Net Virtual Mailing Lists wrote:

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.

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

[Tatsuo Ishii]

> 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.

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 ma*******@postgresql.org

[Bruno Wolff III]

On Sun, Nov 07, 2004 at 09:29:30 +0000,
Jerry III <je******@hotmail.com> wrote:

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?

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