Re: How to avoid using sequential scan

Victor Adolfsson wrote:
> Hi
>
> My query is using a sequential scan and not an index scan even though
> that I
> have indexes defined on the foreign keys.
> This cases my query to take a long long time (10750.687 ms) when it should
> have been completed in less than 1 second.
> Any ideas on what may be the cause of this? I have done a re-index.
>
> Below, I'm including the sql query, the sql schema with indexes and the
> results of the explain, as well as the postgresql version.
>
> -- SQLQUERY
> select datetimestamptz, description from unithistory inner join event on
> event_id=event.id;

You're returning all the rows.

> -- EXPLAIN RESULTS
> Hash Join (cost= 1.12..82296.20 rows=2396163 width=26) (actual time=
> 24.885..8838.418 rows=2396163 loops=1)

I think it's unlikely you'll get 2.4 million rows back in under a
second. Are you sure this is the query you want to test?

--
Richard Huxton
Archonet Ltd