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> (raw)
In-Reply-To: <24b53fa00704091745l560d40ccxec49d7d09e7a97e@mail.gmail.com>
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
> [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
>
>
> Hi Jan,
> Adding this Index slowed down things by a factor of 4.
>
> Also, the performance is so horrible (example bellow) that i am
> certain i am doing something wrong.
>
> Does the following explain gives any ideas ?
>
> Thanks
>
> =# EXPLAIN ANALYZE select * from word_association where (word1 ='the'
> or word2='the') and count > 10;
>
> QUERY PLAN
> --------------------------------------------------------------
> --------------------------------------------------------------
> --------------------
> Bitmap Heap Scan on word_association (cost=250.86..7256.59 rows=4624
> width=22) (actual time=13.461..211.568 rows=6601 loops=1)
> Recheck Cond: (((word1)::text = 'the'::text) OR ((word2)::text =
> 'the'::text))
> Filter: (count > 10)
> -> BitmapOr (cost=250.86..250.86 rows=12243 width=0) (actual
> time=9.052..9.052 rows=0 loops=1)
> -> Bitmap Index Scan on word_association_index1_1
> (cost=0.00..153.20 rows=7579 width=0) (actual time=5.786..5.786
> rows=7232 loops=1)
> Index Cond: ((word1)::text = 'the'::text)
> -> Bitmap Index Scan on word_association_index2_1
> (cost=0.00..95.34 rows=4664 width=0) (actual time=3.253..3.253
> rows=4073 loops=1)
> Index Cond: ((word2)::text = 'the'::text)
> Total runtime: 219.987 ms
> (9 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, s.d.sauron@gmail.com
Subject: Re: Beginner Question
In-Reply-To: <012801c77b7a$33b49e10$2e00a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox