From: Marcus Engene <mengpg2@engene.se>
To: pgsql-performance@postgresql.org
Subject: wildcard makes seq scan on prod db but not in test
Date: Mon, 09 May 2011 20:06:32 +0200
Message-ID: <4DC82D28.7020105@engene.se> (raw)
Dear list,
I have a table with a few million rows and this index:
CREATE INDEX bond_item_common_x7 ON bond_item_common
((lower(original_filename)));
There are about 2M rows on bonddump and 4M rows on bond90.
bonddump is on a 8MB RAM machine, bond90 is on a 72MB RAM machine.
The table is analyzed properly both places.
I'm an index hint zealot, but aware of our different stances in the
matter. :)
Dropping the wildcard for the like, both databases uses the index.
Is there a way to convince Postgres to try not to do full table scan as
much? This is just one of several examples when it happily spends lots
of time sequentially going thru tables.
Thanks,
Marcus
psql (9.0.4)
Type "help" for help.
bonddump=# explain analyze select pic2.objectid
bonddump-# from bond_item_common pic2
bonddump-# where
bonddump-# lower(pic2.original_filename) like 'this is a
test%' ;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using bond_item_common_x7 on bond_item_common pic2
(cost=0.01..8.69 rows=208 width=4) (actual time=26.415..26.415 rows=0
loops=1)
Index Cond: ((lower((original_filename)::text) >= 'this is a
test'::text) AND (lower((original_filename)::text) < 'this is a
tesu'::text))
Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text)
Total runtime: 26.519 ms
(4 rows)
psql (9.0.4)
bond90=> explain analyze select pic2.objectid
bond90-> from bond_item_common pic2
bond90-> where
bond90-> lower(pic2.original_filename) like 'this is a test%' ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on bond_item_common pic2 (cost=0.00..839226.81 rows=475
width=4) (actual time=10599.401..10599.401 rows=0 loops=1)
Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text)
Total runtime: 10599.425 ms
(3 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mengpg2@engene.se
Subject: Re: wildcard makes seq scan on prod db but not in test
In-Reply-To: <4DC82D28.7020105@engene.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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