By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

Can this be indexed?

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
> 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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.