Re: query time

From: WireSpot <wirespot(at)gmail(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: query time
Date: 2005-02-02 15:19:57
Message-ID: b2d4b0380502020719b8611d9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, 02 Feb 2005 14:48:41 +0000, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Think about it, you'd need an index that ordered use_name so that
> (john_doe, AAAAAjohn_doe, Zjohn_doe1234) were all next to each other.
>
> If you anchor the search (LIKE 'john_doe%') and are using the C locale
> then an index can be used (because it's the same as >='john_doe' AND
> <'john_dof').

Unfortunately, all my cases are LIKE '%john_doe'. So I'm guessing I'll
never get an index.

How about the response time when doing both LIKE user_name and ORDER
BY timestamp? Why does it get blown out of the water like that, from 5
and 5 to 2000 ms? If a LIKE by itself takes 5 ms and an ORDER by
itself takes 5 ms... Doesn't it grab the results matching the LIKE and
the ORDER only those?

While we're on the subject of indexes, is there any way I can speed up
a SELECT DISTINCT user_name FROM log_entries? With 200.000 entries I
get like 46 seconds on this one.

I apologise if these things are already in the manual, I'm only now
getting used to it and I don't find some things right away.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2005-02-02 15:29:24 Re: Does indexing help >= as well as = for integer columns?
Previous Message Lonni J Friedman 2005-02-02 15:14:39 capturing/viewing sort_mem utilization on a per query basis