From: Greg Williamson <gwilliamson39@yahoo.com>
To: tuanhoanganh <hatuan05@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL 9.0.1 on Windows performance tunning help please
Date: Fri, 5 Aug 2011 20:09:43 -0700 (PDT)
Message-ID: <1312600183.31294.YahooMailNeo@web46113.mail.sp1.yahoo.com> (raw)
In-Reply-To: <CAJg-yaN9GY2xZ=kCbK0icD6bqzM1nWmDrab7hduQDXLKL=RGxQ@mail.gmail.com>
References: <CAJg-yaMyR03J43RLMToB27YdtK7w-vguot2fYKz+0sFHVRsjwA@mail.gmail.com>
<4E3BE02B020000250003FB8A@gw.wicourts.gov>
<CAJg-yaN9GY2xZ=kCbK0icD6bqzM1nWmDrab7hduQDXLKL=RGxQ@mail.gmail.com>
Tuan --
>
> Thanks for your help.
> I create index on channel_id and data_id like your comment.
...
<...>
>
> explain analyze select d.data_id, d.table_name, d.event_type, d.row_data, d.pk_data, d.old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, > d.source_node_id, d.external_data, '' from sym_data d inner join sym_data_gap g on g.status='GP' and d.data_id between g.start_id and g.end_id where
> d.channel_id='sale_transaction' order by d.data_id asc;
>
> Nested Loop (cost=0.00..1512979014.35 rows=26268463088 width=1401) (actual time=25741.704..7650979.311 rows=2764140 loops=1)
> -> Index Scan using idx_d_channel_id3 on sym_data d (cost=0.00..1781979.40 rows=3117384 width=1401) (actual time=83.718..55126.002 rows=3124631 loops=1)
> -> Index Scan using sym_data_gap_pkey on sym_data_gap g (cost=0.00..358.37 rows=8426 width=8) (actual time=2.428..2.429 rows=1 loops=3124631)
> Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id))
> Filter: (g.status = 'GP'::bpchar)
> Total runtime: 7651803.073 ms
>
> But query performance don't change.
> Please help me.
Did you run an analyze on the table after building the new indexes ? The row estimates seem to be off wildly,
although that may be a symptom of something else and not related, it is worth ruling out the easily tried.
HTH,
Greg Williamson
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: gwilliamson39@yahoo.com, hatuan05@gmail.com, Kevin.Grittner@wicourts.gov
Subject: Re: PostgreSQL 9.0.1 on Windows performance tunning help please
In-Reply-To: <1312600183.31294.YahooMailNeo@web46113.mail.sp1.yahoo.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