Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Cant get planner to use index (7.1.3-1PGDG)

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Orion <orion(at)dusk(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Cant get planner to use index (7.1.3-1PGDG)
Date: 2001-10-12 18:47:40
Message-ID: Pine.BSF.4.21.0110121144300.97689-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
> Now, Here's where things get weird.
> 
> fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date = 
> current_date;
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=13532.12..13532.12 rows=1 width=0)
>   ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..13528.77 rows=1340 
> width=0)
> 
> Here it does a straight date compare and it chooses not to use the index.  
> What??

It's probably deciding that the number of rows (1340) is large enough that
the index scan isn't a win.  Short form is that due to the way things are
structured, after a certain point the index scan becomes more expensive
than sequentially scanning the table.

> fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name = 
> 'SMITH';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=1044.16..1044.16 rows=1 width=0)
>   ->  Index Scan using idx_mfps_orderinfo_435_fname on mfps_orderinfo_435  
> (cost=0.00..1043.47 rows=279 width=0)
> 
> EXPLAIN
> fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name 
> like 'SMITH%';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=12769.48..12769.48 rows=1 width=0)
>   ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=1 width=0)
> 
> EXPLAIN
> fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name 
> like 'SMITH';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=12770.17..12770.17 rows=1 width=0)
>   ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=279 width=0)
> 
> EXPLAIN
> 
> Here it will do an index scan if and only if I use the '=' operator.  If I 
> use like with the % at the end of the string or EVEN if I have no wild card 
> at all... it still does a seq scan.   If anyone has any advice on how to 
> get these indexes working properly, please let me know.

You may want to check your locale setting. IIRC, if you're running with
locale enabled and not in C locale, LIKE does not get optimized to run
with indexes.


In response to

pgsql-bugs by date

Next:From: Dave PageDate: 2001-10-12 19:40:13
Subject: Re: Possible bug in ALTER TABLE RENAME COLUMN (PostgreSQL
Previous:From: Stephan SzaboDate: 2001-10-12 18:22:47
Subject: Re: Bug #480: problem with LIKE pattern matches involving

pgsql-general by date

Next:From: Mihai GheorghiuDate: 2001-10-12 18:57:08
Subject: Error messages
Previous:From: Jelle OuwerkerkDate: 2001-10-12 18:31:46
Subject: SSL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group