Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is this expected? (Index scanning)

Is this expected? (Index scanning)

From
"Mitch Vincent"
Date:
Indexes on both void and lower(cust_name) here..

I get ->

EXPLAIN select * from invoice_master WHERE lower(cust_name) like 'mitch' AND
void = 'f' order by created LIMIT 100;
NOTICE:  QUERY PLAN:

Limit  (cost=2241.98..2241.98 rows=100 width=319)
  ->  Sort  (cost=2241.98..2241.98 rows=604 width=319)
        ->  Index Scan using invoice_cust_name on invoice_master
(cost=0.00..2214.09 rows=604 width=319)

The above query matches zero rows.

EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like '%mitch'
AND void = 'f' ORDER BY created LIMIT 100;
NOTICE:  QUERY PLAN:

Limit  (cost=4310.65..4310.65 rows=1 width=319)
  ->  Sort  (cost=4310.65..4310.65 rows=1 width=319)
        ->  Seq Scan on invoice_master  (cost=0.00..4310.64 rows=1
width=319)

The above query matches zero rows.

EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like 'mitch%'
AND void = 'f' ORDER BY created LIMIT 100;
NOTICE:  QUERY PLAN:

Limit  (cost=2241.98..2241.98 rows=100 width=319)
  ->  Sort  (cost=2241.98..2241.98 rows=604 width=319)
        ->  Index Scan using invoice_cust_name on invoice_master
(cost=0.00..2214.09 rows=604 width=319)

The above query matches 53 rows..

PostgreSQL 7.1.1, FreeBSD 4.2, 73408 records in the invoice_master table and
it's all freshly vacuum analyze 'd...

 I'm just wondering if I'm doing something extremely silly here.. Thanks!!

-Mitch

Re: Is this expected? (Index scanning)

From
Stephan Szabo
Date:
On Sun, 6 May 2001, Mitch Vincent wrote:

> Indexes on both void and lower(cust_name) here..
>
> I get ->
>
> EXPLAIN select * from invoice_master WHERE lower(cust_name) like 'mitch' AND
> void = 'f' order by created LIMIT 100;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=2241.98..2241.98 rows=100 width=319)
>   ->  Sort  (cost=2241.98..2241.98 rows=604 width=319)
>         ->  Index Scan using invoice_cust_name on invoice_master
> (cost=0.00..2214.09 rows=604 width=319)
>
> The above query matches zero rows.
>
> EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like '%mitch'
> AND void = 'f' ORDER BY created LIMIT 100;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=4310.65..4310.65 rows=1 width=319)
>   ->  Sort  (cost=4310.65..4310.65 rows=1 width=319)
>         ->  Seq Scan on invoice_master  (cost=0.00..4310.64 rows=1
> width=319)
>
> The above query matches zero rows.
>
> EXPLAIN SELECT * FROM invoice_master WHERE lower(cust_name) like 'mitch%'
> AND void = 'f' ORDER BY created LIMIT 100;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=2241.98..2241.98 rows=100 width=319)
>   ->  Sort  (cost=2241.98..2241.98 rows=604 width=319)
>         ->  Index Scan using invoice_cust_name on invoice_master
> (cost=0.00..2214.09 rows=604 width=319)
>
> The above query matches 53 rows..

In the first and last case the like is anchored at the beginning so it
uses the name index.  The middle case can't use that index, and how
well distributed are the values of void (is it a boolean?)?  It's likely
that the index isn't very selective, if most of the rows are void='f'
then that index is probably more expensive to scan than the sequence scan
would be.

Re: Is this expected? (Index scanning)

From
"Mitch Vincent"
Date:
> In the first and last case the like is anchored at the beginning so it
> uses the name index.  The middle case can't use that index, and how
> well distributed are the values of void (is it a boolean?)?

Ahh... I thought that the middle case could use an index, I guess that's why
I was confused..

>It's likely
> that the index isn't very selective, if most of the rows are void='f'
> then that index is probably more expensive to scan than the sequence scan
> would be.

Yep, most of the rows are void='f'..

Thanks!!

-Mitch