Re: PostgreSQL 9.0.1 on Windows performance tunning help please

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux