pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQueries not using Index
15+ messages / 5 participants
[nested] [flat]
* Queries not using Index
@ 2002-07-22 18:59 Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 12:56 ` Re: Queries not using Index Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-22 18:59 UTC (permalink / raw)
To: pgsql-sql
Greetings,
I suppose I should have sent this to pgsql-bugs maybe? I would appreciate
it if anybody could help me out. I can't figure out what is going on
here...
snet=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
snet=# \d t2002_06
Table "t2002_06"
Column | Type | Modifiers
---------+--------------------------+-----------
station | character varying(5) |
valid | timestamp with time zone |
tmpf | smallint |
dwpf | smallint |
drct | smallint |
sknt | real |
pday | real |
pmonth | real |
srad | real |
relh | real |
alti | real |
Indexes: t2002_06_station_idx,
t2002_06_tmpf_idx
snet=# \d t2002_06_station_idx;
Index "t2002_06_station_idx"
Column | Type
---------+----------------------
station | character varying(5)
snet=# select count(valid) from t2002_06;
count
---------
1513895
snet=# vacuum analyze t2002_06;
VACUUM
snet=# vacuum t2002_06;
VACUUM
snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4';
NOTICE: QUERY PLAN:
Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47)
EXPLAIN
Shouldn't this be an index scan? I hope that I am not doing something
stupid, although I probably am :(
I even just tried this
drop index t2002_06_station_idx;
vacuum analyze t2002_06;
create index t2002_06_station_idx on t2002_06(station);
vacuum analyze t2002_06;
And I still get a Seq Scan. Augh....
Thanks,
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-22 18:59 Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
@ 2002-07-23 12:56 ` Gaetano Mendola <mendola@bigfoot.com>
2002-07-23 13:26 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Gaetano Mendola @ 2002-07-23 12:56 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; pgsql-sql
"Daryl Herzmann" <akrherz@iastate.edu> wrote:
> snet=# select count(valid) from t2002_06;
> count
> ---------
> 1513895
> snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4';
> NOTICE: QUERY PLAN:
>
> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47)
Can you do the following query for better understand your situation ?
select count(*) from t2002_06 where station = 'SGLI4';
select count(*) from t2002_06;
Ciao
Gaetano.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-22 18:59 Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 12:56 ` Re: Queries not using Index Gaetano Mendola <mendola@bigfoot.com>
@ 2002-07-23 13:26 ` Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 0 replies; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-23 13:26 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
Hi!
Thanks for your help!
On Tue, 23 Jul 2002, Gaetano Mendola wrote:
>"Daryl Herzmann" <akrherz@iastate.edu> wrote:
>> snet=# select count(valid) from t2002_06;
>> count
>> ---------
>> 1513895
>
>> snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4';
>> NOTICE: QUERY PLAN:
>>
>> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47)
>
>
>Can you do the following query for better understand your situation ?
>
>select count(*) from t2002_06 where station = 'SGLI4';
snet=# select count(*) from t2002_06 where station = 'SGLI4';
count
-------
39319
>select count(*) from t2002_06;
snet=# select count(*) from t2002_06;
count
---------
1513895
In another email, it was suggested that I do this...
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual
time=67.89..3734.93 rows=38146 loops=1)
Total runtime: 3748.33 msec
EXPLAIN
snet=# set enable_seqscan=off;
SET VARIABLE
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96
rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1)
Total runtime: 317.76 msec
EXPLAIN
Thanks so much!
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Queries not using Index
@ 2002-07-22 19:07 Daryl Herzmann <akrherz@iitap.iastate.edu>
2002-07-23 15:05 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-22 19:07 UTC (permalink / raw)
To: pgsql-sql
(sorry if you get this twice, my first post bounced since I did not use
the subscribed email account. I assume that those messages get
discarded...)
Greetings,
I suppose I should have sent this to pgsql-bugs maybe? I would appreciate
it if anybody could help me out. I can't figure out what is going on
here...
snet=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
snet=# \d t2002_06
Table "t2002_06"
Column | Type | Modifiers
---------+--------------------------+-----------
station | character varying(5) |
valid | timestamp with time zone |
tmpf | smallint |
dwpf | smallint |
drct | smallint |
sknt | real |
pday | real |
pmonth | real |
srad | real |
relh | real |
alti | real |
Indexes: t2002_06_station_idx,
t2002_06_tmpf_idx
snet=# \d t2002_06_station_idx;
Index "t2002_06_station_idx"
Column | Type
---------+----------------------
station | character varying(5)
snet=# select count(valid) from t2002_06;
count
---------
1513895
snet=# vacuum analyze t2002_06;
VACUUM
snet=# vacuum t2002_06;
VACUUM
snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4';
NOTICE: QUERY PLAN:
Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47)
EXPLAIN
Shouldn't this be an index scan? I hope that I am not doing something
stupid, although I probably am :(
I even just tried this
drop index t2002_06_station_idx;
vacuum analyze t2002_06;
create index t2002_06_station_idx on t2002_06(station);
vacuum analyze t2002_06;
And I still get a Seq Scan. Augh....
Thanks,
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-22 19:07 Queries not using Index Daryl Herzmann <akrherz@iitap.iastate.edu>
@ 2002-07-23 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2002-07-23 15:05 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iitap.iastate.edu>; +Cc: pgsql-sql
On Mon, 22 Jul 2002, Daryl Herzmann wrote:
> snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4';
> NOTICE: QUERY PLAN:
>
> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47)
>
> EXPLAIN
What does explain show if you do set enable_seqscan=off;
?
It's possible that it's estimating a greater cost for the index scan.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
@ 2002-07-23 16:35 Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-23 16:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
>On Tue, 23 Jul 2002, Daryl Herzmann wrote:
>
>> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
>> NOTICE: QUERY PLAN:
>>
>> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual
>> time=67.89..3734.93 rows=38146 loops=1)
>> Total runtime: 3748.33 msec
>>
>> EXPLAIN
>>
>> snet=# set enable_seqscan=off;
>> SET VARIABLE
>> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
>> NOTICE: QUERY PLAN:
>>
>> Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96
>> rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1)
>> Total runtime: 317.76 msec
>
>Looks like the estimated cost is way divorced from reality. Is the
>34979 row estimate even realistic and how well ordered is the table
>(actually output from pg_statistic would be good as well :) ).
Thanks for the help! I am not sure if I can answer your questions. I will
try :)
I believe the row estimate is realistic based on this value.
snet=# select count(*) from t2002_06 WHERE station = 'SAMI4';
count
-------
38146
I am really sorry, but I don't know what to output from pg_statistic. I
searched around on the Internet and was not sure what to send you from
this table. Sorry :(
Thanks!
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
@ 2002-07-23 17:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Stephan Szabo @ 2002-07-23 17:00 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-sql
On Tue, 23 Jul 2002, Daryl Herzmann wrote:
>
> >On Tue, 23 Jul 2002, Daryl Herzmann wrote:
> >
> >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> >> NOTICE: QUERY PLAN:
> >>
> >> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual
> >> time=67.89..3734.93 rows=38146 loops=1)
> >> Total runtime: 3748.33 msec
> >>
> >> EXPLAIN
> >>
> >> snet=# set enable_seqscan=off;
> >> SET VARIABLE
> >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> >> NOTICE: QUERY PLAN:
> >>
> >> Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96
> >> rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1)
> >> Total runtime: 317.76 msec
> >
> >Looks like the estimated cost is way divorced from reality. Is the
> >34979 row estimate even realistic and how well ordered is the table
> >(actually output from pg_statistic would be good as well :) ).
>
> Thanks for the help! I am not sure if I can answer your questions. I will
> try :)
>
> I believe the row estimate is realistic based on this value.
>
> snet=# select count(*) from t2002_06 WHERE station = 'SAMI4';
> count
> -------
> 38146
> I am really sorry, but I don't know what to output from pg_statistic. I
> searched around on the Internet and was not sure what to send you from
> this table. Sorry :(
Right... sorry about that...
select * from pg_statistic where starelid=(select oid from pg_class
where relname='t2002_06';
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-07-23 20:51 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-23 20:51 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi!
Thanks for the continued help.
I have attached the results of your request. Thank you!
Daryl
>Right... sorry about that...
>select * from pg_statistic where starelid=(select oid from pg_class
> where relname='t2002_06';
Attachments:
sql.out.gz (application/x-gzip, 1.3K) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
@ 2002-07-24 00:33 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Stephan Szabo @ 2002-07-24 00:33 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-sql
On Tue, 23 Jul 2002, Daryl Herzmann wrote:
> Hi!
>
> Thanks for the continued help.
>
> I have attached the results of your request. Thank you!
Hmm, when the data was put in, was it put in where the same value
would be bunched up? IIRC that's a case the optimizer won't realize
if the data isn't ordered but merely bunched together that'd cause
it to over-estimate the cost of an index scan. Clustering on the index
might help, but cluster drops alot of info about the table, so you
have to be careful.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-07-24 00:53 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 01:47 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-24 00:53 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Good evening.
On Tue, 23 Jul 2002, Stephan Szabo wrote:
>Hmm, when the data was put in, was it put in where the same value
>would be bunched up?
I inserted the data via 30 "COPY t2002_06 from stdin" (one per day) So it
was grouped by station and then day for each insert. (My script dumped
the data from each station for the day and then repeated for each station
and then finally dumped the entire day into the DB. Are you saying that
this process has tricked pgsql into not believing it needs to use an
INDEX? Sorry for my ignorance here. I have done similar processes with
PG7.1.2 and it seemed to use the INDEX.
In fact, I just repeated the dumping scripts on a machine with 7.1.2 and
the "explain select" reports to be using the Index Scan. Hmmmm
>IIRC that's a case the optimizer won't realize if the data isn't ordered
>but merely bunched together that'd cause it to over-estimate the cost of
>an index scan. Clustering on the index might help, but cluster drops alot
>of info about the table, so you have to be careful.
Thanks for the info. I am off to read about how to do clustering!
Thanks!
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
@ 2002-07-24 01:47 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 01:57 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-24 01:47 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
> I inserted the data via 30 "COPY t2002_06 from stdin" (one per
> day) So it
> was grouped by station and then day for each insert. (My script dumped
> the data from each station for the day and then repeated for each station
> and then finally dumped the entire day into the DB. Are you saying that
> this process has tricked pgsql into not believing it needs to use an
> INDEX? Sorry for my ignorance here. I have done similar processes with
> PG7.1.2 and it seemed to use the INDEX.
>
> In fact, I just repeated the dumping scripts on a machine with 7.1.2 and
> the "explain select" reports to be using the Index Scan. Hmmmm
You _have_ actually run ANALYZE on the table, right?
Chris
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 01:47 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-07-24 01:57 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 02:09 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-24 01:57 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
Hi,
>You _have_ actually run ANALYZE on the table, right?
snet=# vacuum analyze t2002_06;
VACUUM
snet=# vacuum analyze;
VACUUM
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35169 width=47) (actual
time=20.51..1717.78 rows=38146 loops=1)
Total runtime: 1730.63 msec
EXPLAIN
snet=# set enable_seqscan=off;
SET VARIABLE
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132773.85
rows=35169 width=47) (actual time=74.86..299.53 rows=38146 loops=1)
Total runtime: 313.42 msec
EXPLAIN
Any thoughts? I am sorry to be causing all this trouble. I just want my
queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is
now estimated at 1730.63, when I first posted to this list, it was 3900.00
or so. Errrr
Thanks,
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 01:47 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 01:57 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
@ 2002-07-24 02:09 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 02:24 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 1 reply; 15+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-24 02:09 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-sql
Have you tried playing with the statistics gatherer?
From the ANALYZE docs:
"The extent of analysis can be controlled by adjusting the per-column
statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER
TABLE). The target value sets the maximum number of entries in the
most-common-value list and the maximum number of bins in the histogram. The
default target value is 10, but this can be adjusted up or down to trade off
accuracy of planner estimates against the time taken for ANALYZE and the
amount of space occupied in pg_statistic. In particular, setting the
statistics target to zero disables collection of statistics for that column.
It may be useful to do that for columns that are never used as part of the
WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have
no use for statistics on such columns. "
Just a thought...
Also, what is the result of:
select indexdef from pg_indexes where indexname='t2002_06_station_idx';
> Any thoughts? I am sorry to be causing all this trouble. I just want my
> queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is
> now estimated at 1730.63, when I first posted to this list, it
> was 3900.00
> or so. Errrr
It's no trouble. Cases where the planner fails are essential to improving
the planner. Ideally this query should use your index automatically...
Chris
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Queries not using Index
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Re: Queries not using Index Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 01:47 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 01:57 ` Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 02:09 ` Re: Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-07-24 02:24 ` Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 0 replies; 15+ messages in thread
From: Daryl Herzmann @ 2002-07-24 02:24 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
Hi! :)
On Wed, 24 Jul 2002, Christopher Kings-Lynne wrote:
>Have you tried playing with the statistics gatherer?
Nope. I will look at the docs some and play around. This machine is not
fully production yet. :)
>Also, what is the result of:
>select indexdef from pg_indexes where indexname='t2002_06_station_idx';
snet=# select indexdef from pg_indexes where
indexname='t2002_06_station_idx';
indexdef
---------------------------------------------------------------------
CREATE INDEX t2002_06_station_idx ON t2002_06 USING btree (station)
>It's no trouble. Cases where the planner fails are essential to improving
>the planner. Ideally this query should use your index automatically...
Thanks! I have the ~exact~ same database on another machine with PG 7.1.2
and it uses the Index Scan without tweaking. I have never had troubles
with indexes up until this table/database.
I have got another database on the same 7.2.1 machine. It has very
similar data and the same index. It has no trouble defaulting to use the
Index.
awos=# explain analyze select * from t1999_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Index Scan using t1999_06_stn_idx on t1999_06 (cost=0.00..25859.88
rows=36544 width=53) (actual time=152.94..152.94 rows=0 loops=1)
Total runtime: 153.03 msec
EXPLAIN
Augh. Puzzling. Thanks everyone for the help! You all rock!
Daryl
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] Queries not using Index
@ 2002-07-24 15:48 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 15+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-24 15:48 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; 'Daryl Herzmann' <akrherz@iastate.edu>; +Cc: pgsql-sql; pgsql-general <pgsql-general@postgresql.org>
> This SELECT causes a sequention scan 'cause your index
> is not HASH type, but likely a BTREE one.
> BTREE index is to interval searches (station = 'SAMI4%')
> not precise searchs. (station = 'SAMI4').
In Postgres, the hash index is slow and inefficient (it's a bit better
in7.3), and I believe btree is generally recommended over hash even for '='
instances.
Chris
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2002-07-24 15:48 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-22 18:59 Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 12:56 ` Gaetano Mendola <mendola@bigfoot.com>
2002-07-23 13:26 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-22 19:07 Queries not using Index Daryl Herzmann <akrherz@iitap.iastate.edu>
2002-07-23 15:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 16:35 Re: Queries not using Index Daryl Herzmann <akrherz@iastate.edu>
2002-07-23 17:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-23 20:51 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 00:33 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 00:53 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 01:47 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 01:57 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 02:09 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-24 02:24 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 15:48 Re: [SQL] Queries not using Index Christopher Kings-Lynne <chriskl@familyhealth.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