Search Postgresql Archives

Re: [PERFORM] Why Postgres use a little memory on Windows.

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

 



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


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux