From: Adrian Klaver <adrian.klaver@aklaver.com>
To: tuanhoanganh <hatuan05@gmail.com>
To: PostgreSQL <pgsql-general@postgresql.org>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [GENERAL] Why Postgres use a little memory on Windows.
Date: Sat, 20 Feb 2016 10:13:18 -0800
Message-ID: <56C8ACBE.60004@aklaver.com> (raw)
In-Reply-To: <CAJg-yaO8OtzqTxsusesqntPhxG=LDc9skQe3SaVWw30NuWTs5Q@mail.gmail.com>
References: <CAJg-yaO8OtzqTxsusesqntPhxG=LDc9skQe3SaVWw30NuWTs5Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 02/20/2016 08:46 AM, tuanhoanganh wrote:
> 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;
Took liberty of reformatting the above here:
http://sqlformat.darold.net/
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;
The thing that stands out to me is that I do not see that sym_data and
sym_data_gp are actually joined on anything.
Also is it possible to see the schema definitions for the two tables?
>
> 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
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: adrian.klaver@aklaver.com, hatuan05@gmail.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why Postgres use a little memory on Windows.
In-Reply-To: <56C8ACBE.60004@aklaver.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