Performance Issues (was: "like" vs "substring" again)

From: Christian Schröder <cs(at)deriva(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Performance Issues (was: "like" vs "substring" again)
Date: 2007-09-18 17:14:33
Message-ID: 46F00779.4040608@deriva.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list,
I am still fighting with the really slow database queries (see
http://www.nabble.com/%22like%22-vs-%22substring%22-again-t4447906.html)
and I still believe that the cause of the problem is that the query
planner makes incorrect estimations about the selectivity of the "where"
clauses.
I wondered if it is possible to make the query planner perform a
sequential scan over a table *before* it starts planning? If I know that
a table has only about 3000 rows, the overhead due to this sequential
scan can be ignored. On the other hand, this would give the planner an
exact data basis for his planning.
Or would it be possible to tweak how the planner determines the
selectivity? I have read in the docs (chapter 54.1) that in case of more
than one condition in the where clause, independency is assumed. In my
case ("... where test like '11%' and test not like '113%'") this is
clearly not the case, so it might be an interesting point to address.
Do you have any other tips for me?

Kind regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2007-09-18 17:19:04 Re: [GENERAL] ON INSERT => execute AWK/SH/EXE?
Previous Message Tom Lane 2007-09-18 17:09:31 Re: Optimizing "exists"