(long) What's the problem?

From: "David Olbersen" <DOlbersen(at)stbernard(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: (long) What's the problem?
Date: 2003-06-06 00:45:24
Message-ID: E7E213858379814A9AE48CA6754F5ECB0D6EBD@mail01.stbernard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Anybody have any ideas about a problem with this query?

urldb2=> EXPLAIN
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=23.50..23.50 rows=3 width=48)
Sort Key: source
-> Seq Scan on indexscan (cost=0.00..23.47 rows=3 width=48)
Filter: (lower("substring"(urlhost(source), (rposition('www.'::text, (urlhost(source))::character varying) + 2))) ~ '^q.*'::text)
(4 rows)

OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:

urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2-> id,
urldb2-> source,
urldb2-> insertedby,
urldb2-> insertedon,
urldb2-> priority
urldb2-> FROM
urldb2-> indexscan
urldb2-> WHERE
urldb2-> lower(
urldb2-> substring(
urldb2-> urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2-> )
urldb2-> ) ~ '^q.*'
urldb2-> ORDER BY source;

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included that in this email because the error is the same.

urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are trying to use massive amounts of resources. This is a P3 550 with 512MB of RAM.

I can provide more information if needed.

Anybody have ideas about the problem?

--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Joerg Hessdoerfer 2003-06-06 08:30:40 Re: To ListAdms: Is pgsql-sql operating?
Previous Message chester c young 2003-06-05 21:38:07 Domains and Joins