pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated
2+ messages / 2 participants
[nested] [flat]
* Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated
@ 2015-06-12 00:18 Sasa Vilic <sasavilic@gmail.com>
2015-06-15 14:25 ` Re: Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Sasa Vilic @ 2015-06-12 00:18 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated
2015-06-12 00:18 Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated Sasa Vilic <sasavilic@gmail.com>
@ 2015-06-15 14:25 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Merlin Moncure @ 2015-06-15 14:25 UTC (permalink / raw)
To: Sasa Vilic <sasavilic@gmail.com>; +Cc: pgsql-performance
On Thu, Jun 11, 2015 at 7:18 PM, Sasa Vilic <sasavilic@gmail.com> wrote:
> 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)
huh. the query looks pretty clean (except for possible overuse of
surrogate keys which tend to exacerbate planning issues in certain
cases).
Let's try cranking statistics on data.path_id, first to 1000 and then
to 10000 and see how it affects the plan. The database is clearly
misestimating row counts on that join.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-06-15 14:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-12 00:18 Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated Sasa Vilic <sasavilic@gmail.com>
2015-06-15 14:25 ` Merlin Moncure <mmoncure@gmail.com>
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