Message-ID: <557A2555.7090807@gmail.com>
Date: Fri, 12 Jun 2015 02:18:29 +0200
From: Sasa Vilic <sasavilic@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow query: Postgres chooses nested loop over hash join, whery by
 hash join is much faster, wrong number of rows estimated
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

I have a query that takes ridiculously long to complete (over 500ms) but 
if I disable nested loop it does it really fast (24.5ms)

Here are links for
* first request (everything enabled): http://explain.depesz.com/s/Q1M
* second request (nested loop disabled): http://explain.depesz.com/s/9ZY

I have also noticed, that setting

set join_collapse_limit = 1;

produces similar results as when nested loops are disabled.

Autovacuumm is running, and I did manually performed both: analyze and 
vacuumm analyze. No effect.

I tried increasing statistics for columns (slot, path_id, key) to 5000 
for table data. No effect.

I tried increasing statistics for columns (id, parent, key) to 5000 for 
table path. No effect.

I can see, that postgres is doing wrong estimation on request count, but 
I can't figure it out why.

Table path is used to represent tree-like structure.

== QUERY ==

SELECT p1.value as request_type, p2.value as app_id, p3.value as app_ip, 
p3.id as id, data.*, server.name
FROM data
INNER JOIN path p3 ON data.path_id = p3.id
INNER JOIN server on data.server_id = server.id
INNER JOIN path p2 on p2.id = p3.parent
INNER JOIN path p1 on p1.id = p2.parent
WHERE data.slot between '2015-02-18 00:00:00' and '2015-02-19 00:00:00'
     AND p1.key = 'request_type' AND p2.key = 'app_id' AND p3.key = 'app_ip'
;

== TABLES ==
                                              Table "public.path"
  Column |         Type          | Modifiers                     | 
Storage  | Description
--------+-----------------------+---------------------------------------------------+----------+-------------
  id     | integer               | not null default 
nextval('path_id_seq'::regclass) | plain    |
  parent | integer |                                                   | 
plain    |
  key    | character varying(25) | not 
null                                          | extended |
  value  | character varying(50) | not 
null                                          | extended |
Indexes:
     "path_pkey" PRIMARY KEY, btree (id)
     "path_unique" UNIQUE CONSTRAINT, btree (parent, key, value)
Foreign-key constraints:
     "path.fg.parent->path(id)" FOREIGN KEY (parent) REFERENCES path(id)
Referenced by:
     TABLE "data" CONSTRAINT "data_fkey_path" FOREIGN KEY (path_id) 
REFERENCES path(id)
     TABLE "path" CONSTRAINT "path.fg.parent->path(id)" FOREIGN KEY 
(parent) REFERENCES path(id)
Has OIDs: no

                                Table "public.data"
   Column   |              Type              | Modifiers | Storage  | 
Description
-----------+--------------------------------+-----------+----------+-------------
  slot      | timestamp(0) without time zone | not null  | plain    |
  server_id | integer                        | not null  | plain    |
  path_id   | integer                        | not null  | plain    |
  key       | character varying(50)          | not null  | extended |
  value     | real                           | not null  | plain    |
Indexes:
     "data_pkey" PRIMARY KEY, btree (slot, server_id, path_id, key)
Foreign-key constraints:
     "data_fkey_path" FOREIGN KEY (path_id) REFERENCES path(id)
Has OIDs: no

svilic=> select count(*) from path;
  count
-------
    603

svilic=> select count(*) from path p1 inner join path p2 on p1.id = 
p2.parent inner join path p3 on p2.id = p3.parent where p1.parent is null;
  count
-------
    463

svilic=> select count(*) from server;
  count
-------
     37

svilic=> select count(*) from data;
   count
----------
  23495552


svilic=> select version();
version
-------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.1.17 on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

== SERVER CONFIGURATION ==

shared_buffers = 512MB
work_mem = 8MB (I have tried changing it to 32, 128 and 512, no effect)
maintenance_work_mem = 64MB
checkpoint_segments = 100
random_page_cost = 4.0
effective_cache_size = 3072MB

== HARDWARE CONFIGURATION ==

cpu: Intel(R) Core(TM) i3-2100 CPU @ 3.10GHz (4 cores)
mem: 8GB
system is using regular disks, (no raid and no ssd)


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