Is this expected? (Index scanning)

From: "Mitch Vincent" <mitch(at)venux(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Is this expected? (Index scanning)
Date: 2001-05-07 02:19:05
Message-ID: 001b01c0d69c$170b9090$1251000a@windows
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2001-05-07 03:10:02 Re: Is this expected? (Index scanning)
Previous Message Christian Marschalek 2001-05-07 01:22:31 RE: Dead locks