From:
Greg Stark <gsstark(at)mit(dot)edu>
To:
"Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc:
Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject:
Re: Much Ado About COUNT(*)
Date:
2005-01-12 20:08:37
Message-ID:
87k6qiz9yy.fsf@stark.xeocode.com (view raw or flat )
Thread:
2005-01-11 20:15:41 from "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
2005-01-12 08:23:57 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-01-12 08:53:43 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 10:38:55 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 10:17:52 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 15:29:23 from Peter Eisentraut <peter_e(at)gmx(dot)net>
2005-01-12 16:29:34 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 17:20:14 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 17:42:56 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 18:24:26 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 18:55:29 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 19:23:44 from Greg Stark <gsstark(at)mit(dot)edu>
2005-01-12 19:36:15 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 20:08:37 from Greg Stark <gsstark(at)mit(dot)edu>
2005-01-12 20:41:38 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-12 21:43:46 from Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
2005-01-13 02:45:09 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-13 03:19:18 from Greg Stark <gsstark(at)mit(dot)edu>
2005-01-13 03:50:57 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 21:42:06 from "Marek Mosiewicz" <marekmosiewicz(at)poczta(dot)onet(dot)pl>
2005-01-12 19:41:56 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 19:52:53 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 20:09:04 from Rod Taylor <pg(at)rbt(dot)ca>
2005-01-12 20:59:07 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 21:45:51 from Simon Riggs <simon(at)2ndquadrant(dot)com>
2005-01-12 21:58:14 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 22:03:58 from Rod Taylor <pg(at)rbt(dot)ca>
2005-01-12 20:14:51 from "Andrew Dunstan" <andrew(at)dunslane(dot)net>
2005-01-12 20:42:58 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 21:02:23 from Jon Jensen <jon(at)endpoint(dot)com>
2005-01-12 21:06:32 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 21:17:57 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-12 21:36:55 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-12 21:20:43 from Bruno Wolff III <bruno(at)wolff(dot)to>
2005-01-12 21:56:25 from Bruno Wolff III <bruno(at)wolff(dot)to>
2005-01-12 21:48:33 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-18 20:45:50 from Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu>
2005-01-18 22:10:48 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-18 22:29:16 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-18 22:42:32 from Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu>
2005-01-18 23:01:01 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-19 05:09:53 from Christopher Browne <cbbrowne(at)acm(dot)org>
2005-01-12 20:59:51 from Jeff Davis <jdavis-pgsql(at)empires(dot)org>
2005-01-13 10:12:49 from Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
2005-01-13 04:15:58 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-13 04:54:52 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-13 04:57:56 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-13 05:06:38 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-13 05:25:09 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-13 05:39:56 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-13 14:04:46 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-13 15:29:16 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-13 18:22:42 from "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>
2005-01-13 19:20:36 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-16 19:49:45 from Manfred Koizar <mkoi-pg(at)aon(dot)at>
2005-01-16 20:11:37 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-16 20:22:11 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-17 00:53:01 from "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
2005-01-17 01:01:36 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-17 01:24:57 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-01-17 01:28:07 from "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
2005-01-17 01:32:02 from "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
2005-01-17 01:34:58 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-22 21:19:16 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-16 20:38:31 from Jochem van Dieten <jochemd(at)gmail(dot)com>
2005-01-22 20:06:33 from Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
2005-01-23 08:36:10 from Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
2005-01-23 21:36:18 from "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
2005-01-23 23:41:55 from Mark Kirkwood <markir(at)coretech(dot)co(dot)nz>
2005-01-24 15:28:09 from "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
2005-01-25 07:33:57 from Manfred Koizar <mkoi-pg(at)aon(dot)at>
2005-01-25 12:03:28 from Christopher Browne <cbbrowne(at)acm(dot)org>
2005-01-12 18:36:52 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 19:11:32 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 19:59:42 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 20:10:16 from Reinhard Max <max(at)suse(dot)de>
2005-01-12 21:38:44 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2005-01-12 19:28:39 from Kurt Roeckx <Q(at)ping(dot)be>
2005-01-12 19:34:40 from Reinhard Max <max(at)suse(dot)de>
Lists:
pgsql-announce pgsql-hackers pgsql-patches
"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:
> You are correct, I am proposing to add visibility to the indexes.
Then I think the only way you'll get any support is if it's an option. Since
it would incur a performance penalty on updates and deletes.
> As for unqualified counts, I believe that they could take advantage of an
> index-only scan as it requires much less I/O to perform an index scan than a
> sequential scan on large tables.
No, sequential scans require slightly more i/o than index scans. More
importantly they require random access i/o instead of sequential i/o which is
much slower.
Though this depends. If the tuple is very wide then the index might be faster
to scan since it would only contain the data from the fields being indexed.
This brings to mind another approach. It might be handy to split the heap for
a table into multiple heaps. The visibility information would only be in one
of the heaps. This would be a big win if many of the fields were rarely used,
especially if they're rarely used by sequential scans.
> Relation SOME_USERS
> user_id BIGINT PK
> user_nm varchar(32) UNIQUE INDEX
> some_other_attributes...
What's with the fetish with unique indexes? None of this is any different for
unique indexes versus non-unique indexes.
--
greg
In response to
Responses
pgsql-announce by date
Next :From: Rod TaylorDate: 2005-01-12 20:09:04Subject : Re: Much Ado About COUNT(*)Previous :From : Tom LaneDate : 2005-01-12 19:59:42Subject : Re: [HACKERS] segfault caused by heimdal (was: SUSE port)
pgsql-hackers by date
Next :From: Rod TaylorDate: 2005-01-12 20:09:04Subject : Re: Much Ado About COUNT(*)Previous :From : Tom LaneDate : 2005-01-12 19:59:42Subject : Re: [HACKERS] segfault caused by heimdal (was: SUSE port)
pgsql-patches by date
Next :From: Rod TaylorDate: 2005-01-12 20:09:04Subject : Re: Much Ado About COUNT(*)Previous :From : Tom LaneDate : 2005-01-12 19:59:42Subject : Re: [HACKERS] segfault caused by heimdal (was: SUSE port)
