Interesting index/LIKE/join slowness problems

Поиск
Список
Период
Сортировка
От Ole Gjerde
Тема Interesting index/LIKE/join slowness problems
Дата
Msg-id Pine.LNX.4.05.9907151336520.11098-100000@snowman.icebox.org
обсуждение исходный текст
Ответ на Re: [HACKERS] MAX Query length  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: [HACKERS] Interesting index/LIKE/join slowness problems  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Hi,
I've posted 3 messages to pgsql-general about a weird index problem I'm
having.  I've found a very simple case that exhibits this problems.
This time I'm using a different database and different table that the
first 3 messages(It's the same pg install however).

The index called mcrl1_partnumber_index is an index on the 'reference'
field.  The table was just vacuumed(with and without analyze).
The pg install is from CVS last night around 7pm Central time.

The problems seems to be rooted in 'OR' combined with 'LIKE'.  If I remove
the % in the string, explain shows the same (high) cost.  If I also remove
the 'LIKE' the cost basically goes to nothing.  The cost is indeed
correct, either of the 2 first cases takes ~5 minutes, while the last one
(no LIKE) finishes instantly.

The weird thing is, why is the cost being calculated as being that high
when it's actually using the index on that field and is there a reason why
explain shows the index name twice?

I ran the same exact query on a MS SQL server with the same data, and
that took in comparison about 2 seconds to finish.
Both Postgres and MS SQL are on Pentium 100 servers(Yes, very pathetic),
and Linux 2.2.6 and NT 4.0 respectively.

Thanks,
Ole Gjerde

Here's the SQL: 
---------------------
select * from mcrl1 where reference = 'AN914' OR reference LIKE 'AN914-%';

Here's the explain: 
-----------------
mcrl=> explain select * from mcrl1 where reference = 'AN914' OR reference
LIKE AN914-%';
NOTICE:  QUERY PLAN:

Index Scan using mcrl1_reference_index, mcrl1_reference_index on mcrl1
(cost=418431.81 rows=1 width=120)

EXPLAIN

Here's the table layout: 
------------
Table    = mcrl1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |Length|
+----------------------------------+----------------------------------+-------+
| reference                        | varchar()                        |32 |
| cage_num                         | char()                           |5 |
| fsc                              | char()                           |4 |
| niin                             | char()                           |9 |
| isc                              | char()                           |1 |
| rnvc                             | char()                           |1 |
| rncc                             | char()                           |1 |
| sadc                             | char()                           |1 |
| da                               | char()                           |1 |
| description                      | varchar()                        |32 |
+----------------------------------+----------------------------------+-------+
Index:    mcrl1_partnumber_index








В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] migration to v6.5
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] Interesting index/LIKE/join slowness problems