pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about index scan vs seq scan when using count()
7+ messages / 3 participants
[nested] [flat]
* Question about index scan vs seq scan when using count()
@ 2006-02-23 21:44 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-23 21:44 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am using postgres version 7.4, and I read this in the
documentation:
"The aggregate functions sum and count always require a sequential scan
if applied to the entire table."
My understanding of this statement is that if I use count() without a
WHERE clause, then essentially, it is applied to the entire table and
hence requires a seq scan.
But it should not require a seq scan if I have a condition.
For example: I have a table vm_message with an index on column msgid.
Will the following do a sequential scan or an index?
select count(*) from vm_message where msgid = 3;
I used explain, and it said it would do a sequential scan. Why is that?
In fact explain select * from vm_message where msgid = 3 also did a
sequential scan.
What am I doing wrong here? I want it to use my index.
Thanks,
Kashmira
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 22:12 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-02-23 22:12 UTC (permalink / raw)
To: pgsql-sql
On Thu, Feb 23, 2006 at 01:44:43PM -0800, Kashmira Patel (kupatel) wrote:
> My understanding of this statement is that if I use count() without a
> WHERE clause, then essentially, it is applied to the entire table and
> hence requires a seq scan.
> But it should not require a seq scan if I have a condition.
It may not require it, but it might select it anyway.
> For example: I have a table vm_message with an index on column msgid.
> Will the following do a sequential scan or an index?
>
> select count(*) from vm_message where msgid = 3;
How much of the table is that? How many rows? EXPLAIN ANALYSE will
tell you if you have the right plan (estimate vs. actual). The real
question is, are you sure an indexscan is faster?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
It is above all style through which power defers to reason.
--J. Robert Oppenheimer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 22:25 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-23 22:25 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
> For example: I have a table vm_message with an index on column msgid.
> Will the following do a sequential scan or an index?
>
> select count(*) from vm_message where msgid = 3;
How much of the table is that? How many rows? EXPLAIN ANALYSE will
tell you if you have the right plan (estimate vs. actual). The real
question is, are you sure an indexscan is faster?
[Kashmira] I did do an EXPLAIN ANALYZE as well, it also showed a
sequential scan. The table has about 600+ rows, with around 6 of them
matching the given id. Wouldn't an index scan be faster in this case?
Also, I have two more indices defined on this table, for other types of
queries I do on it. Would they be causing a problem? In general, is
there a rule of thumb as to when an index scan would be better than a
sequential scan?
Thanks,
Kashmira
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 22:35 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-23 22:35 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; pgsql-sql
The index has been around since I first created the table. I did VACUUM
ANALYZE, that should have taken care of analyzing this table, right?
-----Original Message-----
From: Tomas Vondra [mailto:tv@fuzzy.cz]
Sent: Thursday, February 23, 2006 2:29 PM
To: Kashmira Patel (kupatel)
Subject: Re: [SQL] Question about index scan vs seq scan when using
count()
> What am I doing wrong here? I want it to use my index.
For small tables the sequential scan is faster, that means less disk
reads is required the whole table than to use the index.
If it is a large table, the index should be used. Have you created the
index recently? Have you analyzed the table since that time (ANALYZE
tablename)
t.v.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 22:47 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-02-23 22:47 UTC (permalink / raw)
To: pgsql-sql
On Thu, Feb 23, 2006 at 02:25:34PM -0800, Kashmira Patel (kupatel) wrote:
>
> [Kashmira] I did do an EXPLAIN ANALYZE as well, it also showed a
> sequential scan. The table has about 600+ rows, with around 6 of them
> matching the given id. Wouldn't an index scan be faster in this case?
EXPLAIN ANALYSE will always choose the same plan as EXPLAIN. The
difference is that it shows you the estimate and actual.
I am surprised you're getting a seqscan for that, though. Is there
something about the index you're not telling us?
Is your system tuned correctly? Maybe 600 rows is so small that a
seqscan's just as fast.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 22:55 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-23 22:55 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
This is how I created the index:
CREATE INDEX msgid_index ON vm_message(msgid);
I guess it doing this because its a small table then. I will try putting
more values.
Thanks,
Kashmira
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Thursday, February 23, 2006 2:47 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question about index scan vs seq scan when using
count()
On Thu, Feb 23, 2006 at 02:25:34PM -0800, Kashmira Patel (kupatel)
wrote:
>
> [Kashmira] I did do an EXPLAIN ANALYZE as well, it also showed a
> sequential scan. The table has about 600+ rows, with around 6 of them
> matching the given id. Wouldn't an index scan be faster in this case?
EXPLAIN ANALYSE will always choose the same plan as EXPLAIN. The
difference is that it shows you the estimate and actual.
I am surprised you're getting a seqscan for that, though. Is there
something about the index you're not telling us?
Is your system tuned correctly? Maybe 600 rows is so small that a
seqscan's just as fast.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Question about index scan vs seq scan when using count()
@ 2006-02-23 23:08 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 7+ messages in thread
From: Owen Jacobson @ 2006-02-23 23:08 UTC (permalink / raw)
To: Kashmira Patel \(kupatel\) <kupatel@cisco.com>; Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
Kashmira Patel wrote:
> I did do an EXPLAIN ANALYZE as well, it also showed a
> sequential scan. The table has about 600+ rows, with around 6 of them
> matching the given id. Wouldn't an index scan be faster in this case?
Not necessarily. It's entirely possible, if your rows are small, that 600 rows will fit on a single disk page. The index will be stored on a(t least one) separate disk page. The cost of loading a page from disk pretty much swamps the cost of processing rows on a page, so in general the server tries to minimize the number of pages used. To use an index for a one-page table, it'd have to load two pages (the table and the index); to do a sequential scan over a one-page table it only has to load the table.
Indexes are useful because they allow the DB to reduce the total number of pages loaded to complete a query.
-Owen
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-02-23 23:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-23 21:44 Question about index scan vs seq scan when using count() Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-23 22:12 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-23 22:25 Re: Question about index scan vs seq scan when using count() Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-23 22:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-23 22:35 Re: Question about index scan vs seq scan when using count() Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-23 22:55 Re: Question about index scan vs seq scan when using count() Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-23 23:08 Re: Question about index scan vs seq scan when using count() Owen Jacobson <ojacobson@osl.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