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