From: "Dave Dutcher" <dave@tridecap.com>
To: "'s d'" <s.d.sauron@gmail.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Beginner Question
Date: Tue, 10 Apr 2007 09:11:57 -0500
Message-ID: <012801c77b7a$33b49e10$2e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <24b53fa00704091745l560d40ccxec49d7d09e7a97e@mail.gmail.com>
Thread-Index: Acd7Ce6FdgFYPYF4RgmC+rvxH+strAAbYFwA
Importance: Normal

In your first post you said that the query is taking much longer than a
second, and in your second post you say the performance is horrible, but
explain analyze shows the query runs in 219 milliseconds, which doesn't =
seem
too bad to me.  I wonder if the slow part for you is returning all the =
rows
to the client?  How are you running this query?  (JDBC, ODBC, C =
library?)
Do you really need all the rows?  Maybe you could use a cursor to page
through the rows?

Dave


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of s d
> Sent: Monday, April 09, 2007 7:46 PM
> To: Jan de Visser
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Beginner Question
>=20
>=20
> Hi Jan,
> Adding this Index slowed down things by a factor of 4.
>=20
> Also, the performance is so horrible (example bellow) that i am
> certain i am doing something wrong.
>=20
> Does the following explain gives any ideas ?
>=20
> Thanks
>=20
> =3D#  EXPLAIN ANALYZE select * from word_association where (word1 =
=3D'the'
> or word2=3D'the') and count > 10;
>=20
> QUERY PLAN
> --------------------------------------------------------------
> --------------------------------------------------------------
> --------------------
>  Bitmap Heap Scan on word_association  (cost=3D250.86..7256.59 =
rows=3D4624
> width=3D22) (actual time=3D13.461..211.568 rows=3D6601 loops=3D1)
>    Recheck Cond: (((word1)::text =3D 'the'::text) OR ((word2)::text =
=3D
> 'the'::text))
>    Filter: (count > 10)
>    ->  BitmapOr  (cost=3D250.86..250.86 rows=3D12243 width=3D0) =
(actual
> time=3D9.052..9.052 rows=3D0 loops=3D1)
>          ->  Bitmap Index Scan on word_association_index1_1
> (cost=3D0.00..153.20 rows=3D7579 width=3D0) (actual =
time=3D5.786..5.786
> rows=3D7232 loops=3D1)
>                Index Cond: ((word1)::text =3D 'the'::text)
>          ->  Bitmap Index Scan on word_association_index2_1
> (cost=3D0.00..95.34 rows=3D4664 width=3D0) (actual time=3D3.253..3.253
> rows=3D4073 loops=3D1)
>                Index Cond: ((word2)::text =3D 'the'::text)
>   Total runtime: 219.987 ms
> (9 rows)