pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex question
5+ messages / 3 participants
[nested] [flat]
* index question
@ 2001-10-19 00:25 Joseph Shraibman <jks@selectacast.net>
2001-10-19 01:39 ` Re: index question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Joseph Shraibman @ 2001-10-19 00:25 UTC (permalink / raw)
To: pgsql-sql
Lets say I have a table with columns a and b. I want to do a query like
SELECT count(distinct b) WHERE a = 2;
Should I have an index on a or an index on (a,b)?
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index question
2001-10-19 00:25 index question Joseph Shraibman <jks@selectacast.net>
@ 2001-10-19 01:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-10-19 01:39 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
Joseph Shraibman <jks@selectacast.net> writes:
> Lets say I have a table with columns a and b. I want to do a query like
> SELECT count(distinct b) WHERE a = 2;
> Should I have an index on a or an index on (a,b)?
At present, only an index on a will help any. Our implementation of
count(distinct ...) isn't bright enough to make use of indexes.
Now an index on (a,b) can substitute for an index on a, so if you
have other queries that could use both columns of the (a,b) index
then it might be worth making that instead of an index on a.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Index question
@ 2004-02-13 02:30 David Witham <davidw@unidial.com.au>
2004-02-13 05:38 ` Re: Index question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David Witham @ 2004-02-13 02:30 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a table with around 3M records in it and a few indexes on it. One of them is on the day column. I get 10-20K new records a day. After running ANALYSE in psql I tried the following queries:
buns=# explain select count(*) from cdr where day >= '20040127';
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=85596.50..85596.50 rows=1 width=0)
-> Seq Scan on cdr (cost=0.00..85053.86 rows=217055 width=0)
Filter: ("day" >= '2004-01-27'::date)
(3 rows)
buns=# explain select count(*) from cdr where day = '20040127';
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=12950.10..12950.10 rows=1 width=0)
-> Index Scan using cdr_ix1 on cdr (cost=0.00..12928.00 rows=8839 width=0)
Index Cond: ("day" = '2004-01-27'::date)
(3 rows)
buns=# explain select count(*) from cdr where day between '20040127' and current_date;
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=20129.91..20129.91 rows=1 width=0)
-> Index Scan using cdr_ix1 on cdr (cost=0.00..20095.66 rows=13699 width=0)
Index Cond: (("day" >= '2004-01-27'::date) AND ("day" <= ('now'::text)::date))
(3 rows)
I understand that selecting count(*) will involve a scan at some stage, but I was surprised that the index wasn't used in the >= case, but was used in the between case.
Why is this so? Do I need to ANALYSE some more or is this just the way the query planner works?
Thanks,
David
David Witham
Telephony Platforms Architect
Unidial
Ph: 03 8628 3383
Fax: 03 8628 3399
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index question
2004-02-13 02:30 Index question David Witham <davidw@unidial.com.au>
@ 2004-02-13 05:38 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-02-13 05:38 UTC (permalink / raw)
To: David Witham <davidw@unidial.com.au>; +Cc: pgsql-sql
"David Witham" <davidw@unidial.com.au> writes:
> I understand that selecting count(*) will involve a scan at some
> stage, but I was surprised that the index wasn't used in the >= case,
> but was used in the between case.
Given the estimated row counts in your examples, the planner's choices
are not surprising. You have not given us any information on whether
those estimates are accurate.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index question
@ 2004-02-13 05:58 David Witham <davidw@unidial.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: David Witham @ 2004-02-13 05:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
There are 18321 records for 20040127 and so the estimate of 8839 for the = case is low but it still does the right thing.
There are 227197 records between '20040127' and current_date so the estimate in the >= case is accurate but the estimate for the between case is an order of magnitude too low. However, it used the index I wanted and the >= case didn't.
Regards,
David
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, 13 February 2004 16:38
To: David Witham
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Index question
"David Witham" <davidw@unidial.com.au> writes:
> I understand that selecting count(*) will involve a scan at some
> stage, but I was surprised that the index wasn't used in the >= case,
> but was used in the between case.
Given the estimated row counts in your examples, the planner's choices
are not surprising. You have not given us any information on whether
those estimates are accurate.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-02-13 05:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-19 00:25 index question Joseph Shraibman <jks@selectacast.net>
2001-10-19 01:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 02:30 Index question David Witham <davidw@unidial.com.au>
2004-02-13 05:38 ` Re: Index question Tom Lane <tgl@sss.pgh.pa.us>
2004-02-13 05:58 Re: Index question David Witham <davidw@unidial.com.au>
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