Re: Or selection on index versus union

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: han(dot)holl(at)informationslogik(dot)nl
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Or selection on index versus union
Date: 2005-10-04 21:08:38
Message-ID: 20051004210838.GA72829@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Oct 04, 2005 at 09:32:41PM +0200, han(dot)holl(at)informationslogik(dot)nl wrote:
> I've got a table with an index, let's call it fase.
>
> The following query is fine: 'select something from table where fase = '1';
>
> However, this is disastrously slow:
> select something from table where fase = '1' or fase = '2';

Could we see some EXPLAIN ANALYZE output? What version of PostgreSQL
are you using? Have you run VACUUM ANALYZE on the table to remove
dead tuples and update the statistics? Have you considered clustering
the table on fase's index?

It might be useful to see the output of the following queries,
assuming the table name is foo:

SET enable_seqscan TO on;
SET enable_indexscan TO off;
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '1';
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '2';
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '1' OR fase = '2';

SET enable_seqscan TO off;
SET enable_indexscan TO on;
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '1';
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '2';
EXPLAIN ANALYZE SELECT something FROM foo WHERE fase = '1' OR fase = '2';

SHOW random_page_cost;
SHOW effective_cache_size;
SELECT version();

\x
SELECT * FROM pg_stats WHERE tablename = 'foo' AND attname = 'fase';

BTW, pgsql-performance is a list dedicated to performance issues,
so you might want to ask performance-related questions there.

--
Michael Fuhr

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew Terenzio 2005-10-04 21:18:56 Re: License question
Previous Message han.holl 2005-10-04 20:57:47 Re: Or selection on index versus union