Re: The standard 'why does it take so long' question

Nigel J. Andrews wrote:
> The query refered to in the post below:
>
> explain analyze
>  select u.name
>         , p.session_id
>         , p.post_number
>         , to_char(p.time,'Dy, Mon DD YYYY HH:MIam ET')
>    from chat_post p
>         , chat_user u
>    where
>         p.poster_id = u.id
>        AND
>         p.time >= 'epoch'::timestamptz + '959904000 seconds'::interval
>        and
>         p.time <= 'epoch'::timestamptz + '1023667200 seconds'::interval
>        and
>         ( u.lower_name = 'thrifty' OR u.lower_name = 'hope1' )
>    order by p.time
>

I'm not sure I can answer your detailed questions, but if you're really
searching for a way to make this query run faster, it strikes me that I
didn't see any evidence of an index on p.poster_id.

It looks to me like that would speed this query significantly. For
example, I just experimented here with a header/detail query similar to
yours, and found that without an index on the foreign key field, the
time for the query increased by 1000X (1.74 msec to 1771.95 msec).

HTH,

Joe