Обсуждение: Port Bug Report: Wild memory use for badly written SQL

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Urban Widmark
Your email address      : urban@svenskatest.se

Category                : runtime: back-end: SQL
Severity                : serious

Summary: Wild memory use for badly written SQL

System Configuration
--------------------
  Operating System   : Linux 2.2.x & 2.3.x

  PostgreSQL version : 6.5beta and 6.4.2 (I think)

  Compiler used      : gcc 2.7.2.3

Hardware:
---------
Linux tux.svenskatest.se 2.2.7 #1 Sun May 2 12:04:13 CEST 1999 i586 unknown
Upgraded RedHat 5.0
Pentium, 32M RAM, IDE, eepro 10/100 pci

Versions of other tools:
------------------------
GNU tools (make, flex, ...)

--------------------------------------------------------------------------

Problem Description:
--------------------
Memory usage for the postmaster process is huge (more than
the ~300M virtually available) for a certain SQL statement,
even with empty data tables.

I find it unlikely that it should need that much space to
handle the statement on an empty table.

This is especially bad on systems where the kernel kills a
random process when running out of memory (eg Linux 2.2/2.3).


--------------------------------------------------------------------------

Test Case:
----------
JDBC access to the following table:

Table    = ts_syllabus
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| ts_lang                          | varchar() not null               |    50 |
| ts_key                           | varchar() not null               |    50 |
| ts_val                           | varchar()                        |  2000 |

select * from ts_syllabus where
  (ts_key like '1%' and ts_lang='swe') or
  (ts_key like '2%' and ts_lang='swe') or
  (ts_key like '3%' and ts_lang='swe') or
  (ts_key like '4%' and ts_lang='swe') or
  (ts_key like '5%' and ts_lang='swe') or
  (ts_key like '6%' and ts_lang='swe') or
  (ts_key like '7%' and ts_lang='swe') or
  (ts_key like '8%' and ts_lang='swe') or
  (ts_key like '9%' and ts_lang='swe')


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------

> select * from ts_syllabus where
>   (ts_key like '1%' and ts_lang='swe') or
>   (ts_key like '2%' and ts_lang='swe') or
>   (ts_key like '3%' and ts_lang='swe') or
>   (ts_key like '4%' and ts_lang='swe') or
>   (ts_key like '5%' and ts_lang='swe') or
>   (ts_key like '6%' and ts_lang='swe') or
>   (ts_key like '7%' and ts_lang='swe') or
>   (ts_key like '8%' and ts_lang='swe') or
>   (ts_key like '9%' and ts_lang='swe')

This is a mix of two issues, the indexing of LIKE converts the above to
a more complex case that can use indexes, and OR's explode the query
optimizer's conversion of the where clause to CNF format.  Not much we
can do in the short term to fix this.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, 2 Jun 1999, Bruce Momjian wrote:

> optimizer's conversion of the where clause to CNF format.  Not much we
> can do in the short term to fix this.
>

That's fine ... the statement was rewritten so it is not an issue for
me anymore, except that another similar statement might be generated and
stop postgreSQL from doing anything useful.
(and on recent Linux kernels, the machine from doing anything useful ...)

/Urban