Re: PostgreSQL 9.0.1 on Windows performance tunning help please

Yes, I run
VACUUM VERBOSE ANALYZE sym_data;
VACUUM VERBOSE ANALYZE sym_data_gap;
after create index.

If i remove ORDER BY, the query run faster.

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';

Nested Loop (cost=0.00..1384889042.54 rows=26266634550 width=1400) (actual
time=63.546..36699.188 rows=2764140 loops=1)
-> Index Scan using idx_dg_status on sym_data_gap g (cost=0.00..2802.42
rows=75838 width=8) (actual time=63.348..122.565 rows=75838 loops=1)
Index Cond: (status = 'GP'::bpchar)
-> Index Scan using idx_d_channel_id3 on sym_data d (cost=0.00..13065.83
rows=346352 width=1400) (actual time=0.027..0.450 rows=36 loops=75838)
Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id))
Total runtime: 37226.543 ms

On Sat, Aug 6, 2011 at 10:09 AM, Greg Williamson <gwilliamson39(at)yahoo(dot)com>wrote:

>
> 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
>
>