pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feeddocs update for count(*) and index-only scans
9+ messages / 5 participants
[nested] [flat]
* docs update for count(*) and index-only scans
@ 2011-11-01 22:26 Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Kupershmidt @ 2011-11-01 22:26 UTC (permalink / raw)
To: pgsql-docs
Hi all,
func.sgml still claims that a sequential scan is the only way to
execute a SELECT COUNT(*) query. I think this note should just be
removed from the current docs, given the existence of index-only
scans; patch attached.
I didn't see any other outright incorrect spots regarding COUNT(*).
There are two EXPLAIN outputs for COUNT(*) queries, in
auto-explain.sgml and ddl.sgml, though those are probably fine as-is.
Josh
Attachments:
func.sgml.diff (application/octet-stream, 874B) download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 2b8298c..c51d122
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT b1 = ANY((SELECT b2 FROM t2 ...))
*** 10761,10780 ****
</para>
</note>
- <note>
- <para>
- Users accustomed to working with other SQL database management
- systems might be disappointed by the performance of the
- <function>count</function> aggregate when it is applied to the
- entire table. A query like:
- <programlisting>
- SELECT count(*) FROM sometable;
- </programlisting>
- will be executed by <productname>PostgreSQL</productname> using a
- sequential scan of the entire table.
- </para>
- </note>
-
<para>
The aggregate functions <function>array_agg</function>,
<function>string_agg</function>,
--- 10761,10766 ----
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
@ 2011-11-01 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2011-11-01 22:51 UTC (permalink / raw)
To: Josh Kupershmidt <schmiddy@gmail.com>; +Cc: pgsql-docs
Josh Kupershmidt <schmiddy@gmail.com> writes:
> func.sgml still claims that a sequential scan is the only way to
> execute a SELECT COUNT(*) query. I think this note should just be
> removed from the current docs, given the existence of index-only
> scans; patch attached.
Well, it might need adjustment, but I don't think we should remove it
outright. The people who complain that COUNT(*) is not O(1) are still
going to be complaining. On tables that are not read-mostly, there's
no reason to expect that index-only scans will even provide a material
speed boost, let alone be close to free.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-11-01 23:16 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Kupershmidt @ 2011-11-01 23:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Tue, Nov 1, 2011 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Kupershmidt <schmiddy@gmail.com> writes:
>> func.sgml still claims that a sequential scan is the only way to
>> execute a SELECT COUNT(*) query. I think this note should just be
>> removed from the current docs, given the existence of index-only
>> scans; patch attached.
>
> Well, it might need adjustment, but I don't think we should remove it
> outright. The people who complain that COUNT(*) is not O(1) are still
> going to be complaining. On tables that are not read-mostly, there's
> no reason to expect that index-only scans will even provide a material
> speed boost, let alone be close to free.
Yeah, that's all true. I'd be OK with an adjustment along the lines of
"note: COUNT(*) can be expensive, use judiciously".
But the tone of the existing note suggests that users "may be
surprised" that our COUNT(*) is slower than other RDBMSs. So I guess
I'm wondering, are we really still that much slower than our
competitors for COUNT(*)? Ignoring MyISAM and similar lobotomized
engines, does any competitor have a much-faster way?
Josh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
@ 2011-11-02 00:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2011-11-02 00:07 UTC (permalink / raw)
To: Josh Kupershmidt <schmiddy@gmail.com>; +Cc: pgsql-docs
Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Tue, Nov 1, 2011 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, it might need adjustment, but I don't think we should remove it
>> outright. The people who complain that COUNT(*) is not O(1) are still
>> going to be complaining. On tables that are not read-mostly, there's
>> no reason to expect that index-only scans will even provide a material
>> speed boost, let alone be close to free.
> But the tone of the existing note suggests that users "may be
> surprised" that our COUNT(*) is slower than other RDBMSs. So I guess
> I'm wondering, are we really still that much slower than our
> competitors for COUNT(*)? Ignoring MyISAM and similar lobotomized
> engines, does any competitor have a much-faster way?
It's the "lobotomized engines" that are the problem, IMO --- people
coming from databases like mysql tend to think count(*) just means
reading a table size counter that the engine has anyway.
I'm not really sure how our index-only scans stack up against the likes
of Oracle. For a read-mostly table they probably have to do a
comparable amount of work, but I'm not real sure how occasional heap
visits (for us) will compare to occasional rollback segement visits (for
them). And they may be able to scan their indexes in physical order,
which would help a fair amount. And the whole code path is probably a
good bit more mature and better optimized on that side of the fence,
too.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-11-04 20:10 ` Robert Haas <robertmhaas@gmail.com>
2011-11-04 21:30 ` Re: docs update for count(*) and index-only scans Scott Marlowe <scott.marlowe@gmail.com>
2011-12-06 03:14 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Robert Haas @ 2011-11-04 20:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Kupershmidt <schmiddy@gmail.com>; pgsql-docs
On Tue, Nov 1, 2011 at 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's the "lobotomized engines" that are the problem, IMO --- people
> coming from databases like mysql tend to think count(*) just means
> reading a table size counter that the engine has anyway.
This is probably a much less common misconception than formerly, due
to the rise of InnoDB and the falling-out-of-favor experienced by
MyISAM.
I think some pessimism removal is probably warranted. Yeah, somebody
else might be faster than us on this test, but that's probably true of
many tests. And on others we will be faster than them.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
@ 2011-11-04 21:30 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2011-11-04 21:30 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Kupershmidt <schmiddy@gmail.com>; pgsql-docs
On Fri, Nov 4, 2011 at 2:10 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Nov 1, 2011 at 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's the "lobotomized engines" that are the problem, IMO --- people
>> coming from databases like mysql tend to think count(*) just means
>> reading a table size counter that the engine has anyway.
>
> This is probably a much less common misconception than formerly, due
> to the rise of InnoDB and the falling-out-of-favor experienced by
> MyISAM.
True. For instance the Drizzle project simply yanked myisam as an
engine out of mysql for their fork, and made innodb the standard
default table handler.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
@ 2011-12-06 03:14 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-12-06 16:27 ` Re: docs update for count(*) and index-only scans Marti Raudsepp <marti@juffo.org>
1 sibling, 1 reply; 9+ messages in thread
From: Josh Kupershmidt @ 2011-12-06 03:14 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Fri, Nov 4, 2011 at 4:10 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Nov 1, 2011 at 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's the "lobotomized engines" that are the problem, IMO --- people
>> coming from databases like mysql tend to think count(*) just means
>> reading a table size counter that the engine has anyway.
>
> This is probably a much less common misconception than formerly, due
> to the rise of InnoDB and the falling-out-of-favor experienced by
> MyISAM.
>
> I think some pessimism removal is probably warranted. Yeah, somebody
> else might be faster than us on this test, but that's probably true of
> many tests. And on others we will be faster than them.
So, if Tom still wants to keep that paragraph and its warning, how
about we just fix the now-incorrect bit at the end? Maybe just tweak
"will be executed" to "will often be executed", or change "using a
sequential scan of the entire table." to "using a sequential scan of
the table, or an index-only scan of one of its indexes".
Josh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
2011-12-06 03:14 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
@ 2011-12-06 16:27 ` Marti Raudsepp <marti@juffo.org>
2012-01-27 17:43 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Marti Raudsepp @ 2011-12-06 16:27 UTC (permalink / raw)
To: Josh Kupershmidt <schmiddy@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Tue, Dec 6, 2011 at 05:14, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> Maybe just tweak
> "will be executed" to "will often be executed", or change "using a
> sequential scan of the entire table." to "using a sequential scan of
> the table, or an index-only scan of one of its indexes".
I don't think we need to specify what the planner does at all. How
about simply "will need to access all rows [in the table]"
Also +1 for removing references to "other SQL databases".
Regards,
Marti
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: docs update for count(*) and index-only scans
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Re: docs update for count(*) and index-only scans Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Re: docs update for count(*) and index-only scans Robert Haas <robertmhaas@gmail.com>
2011-12-06 03:14 ` Re: docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-12-06 16:27 ` Re: docs update for count(*) and index-only scans Marti Raudsepp <marti@juffo.org>
@ 2012-01-27 17:43 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Robert Haas @ 2012-01-27 17:43 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: Josh Kupershmidt <schmiddy@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Tue, Dec 6, 2011 at 11:27 AM, Marti Raudsepp <marti@juffo.org> wrote:
> On Tue, Dec 6, 2011 at 05:14, Josh Kupershmidt <schmiddy@gmail.com> wrote:
>> Maybe just tweak
>> "will be executed" to "will often be executed", or change "using a
>> sequential scan of the entire table." to "using a sequential scan of
>> the table, or an index-only scan of one of its indexes".
>
> I don't think we need to specify what the planner does at all. How
> about simply "will need to access all rows [in the table]"
>
> Also +1 for removing references to "other SQL databases".
I just modified this so that it's not outright wrong any more. I
think it's still more pessimistic than is warranted, but I wasn't sure
exactly how to rephrase it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2012-01-27 17:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-01 22:26 docs update for count(*) and index-only scans Josh Kupershmidt <schmiddy@gmail.com>
2011-11-01 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-01 23:16 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-11-02 00:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-04 20:10 ` Robert Haas <robertmhaas@gmail.com>
2011-11-04 21:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-12-06 03:14 ` Josh Kupershmidt <schmiddy@gmail.com>
2011-12-06 16:27 ` Marti Raudsepp <marti@juffo.org>
2012-01-27 17:43 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox