Re: How to avoid using sequential scan

From: Richard Huxton <dev(at)archonet(dot)com>
To: Victor Adolfsson <victor(at)optimumbiometrics(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to avoid using sequential scan
Date: 2007-02-12 12:38:25
Message-ID: 45D05FC1.5090503@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Shane Ambler 2007-02-12 14:02:46 Re: getting postgres to emulate mysql/sqlserver bit datatype
Previous Message Victor Adolfsson 2007-02-12 12:26:12 How to avoid using sequential scan