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 по дате отправления: