From: | tuanhoanganh <hatuan05(at)gmail(dot)com> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Why Postgres use a little memory on Windows. |
Date: | 2016-02-20 16:46:38 |
Message-ID: | CAJg-yaO8OtzqTxsusesqntPhxG=LDc9skQe3SaVWw30NuWTs5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance |
Hello
I have a bad query on PostgreSQL 9.0.23 - 64bit - Windows 2012 R2 - 48GB Ram
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;
Here is result
Nested Loop (cost=319.42..4879348246.58 rows=32820035265 width=1525)
(actual time=64656.747..5594654.189 rows=3617090 loops=1)
-> Index Scan using sym_data_pkey on sym_data d
(cost=0.00..3671742.82 rows=3867095 width=1525) (actual
time=9.775..12465.153 rows=3866359 loops=1)
Filter: ((channel_id)::text = 'sale_transaction'::text)
-> Bitmap Heap Scan on sym_data_gap g (cost=319.42..1133.51
rows=8487 width=8) (actual time=1.438..1.439 rows=1 loops=3866359)
Recheck Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id))
Filter: (g.status = 'GP'::bpchar)
-> Bitmap Index Scan on sym_data_gap_pkey (cost=0.00..317.30
rows=8487 width=0) (actual time=1.436..1.436 rows=1 loops=3866359)
Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <=
g.end_id))
http://explain.depesz.com/s/c3DT
I have run vaccum full. Here is my PostgreSQL config
shared_buffers = 2GB
work_mem = 64MB
maintenance_work_mem = 1GB
wal_buffers = 256
effective_cache_size = 4GB
checkpoint_segments = 256
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 256
random_page_cost = 3.5
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 250
max_locks_per_transaction = 2000
When I check taskmanager, I found postgres process is user 4-5MB
What happened with my PostgreSQL. Please help me
Thank you in advance.
Tuan Hoang Anh
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2016-02-20 17:09:01 | Re: JDBC behaviour |
Previous Message | Vitalii Tymchyshyn | 2016-02-20 16:29:09 | Re: JDBC behaviour |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-02-20 18:13:18 | Re: [GENERAL] Why Postgres use a little memory on Windows. |
Previous Message | Jim Nasby | 2016-02-18 20:33:48 | Re: Architectural question |