pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #14438: Wrong row count in the join plan with unique index scan
3+ messages / 3 participants
[nested] [flat]
* BUG #14438: Wrong row count in the join plan with unique index scan
@ 2016-11-28 18:21 nikolay.nikitin@infowatch.com
2016-11-29 23:07 ` Re: BUG #14438: Wrong row count in the join plan with unique index scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: nikolay.nikitin@infowatch.com @ 2016-11-28 18:21 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged on the website:
Bug reference: 14438
Logged by: Nikolay Nikitin
Email address: nikolay.nikitin@infowatch.com
PostgreSQL version: 9.6.1
Operating system: Red Hat server 6.7
Description:
Hi,
I execute select with "in" condition on the empty table.
This select works with wrong plan.
I think the wrong plan is chosen becose the postgres optimizer incorrectly
calculates row count in the right plan.
Sample:
drop table if exists test_main;
-- main table with many data rows
create table test_main
(
id numeric(20) not null,
tms timestamp not null,
primary key (id)
);
create index idx_test_main_tms on test_main(tms desc);
insert into test_main(id, tms)
select a, current_date + (trunc(random() * 3 * 24 * 60) || '
minutes')::interval
from generate_series(1, 100000) a;
analyze test_main;
drop table if exists test_slave;
-- empty table test_slave
create table test_slave
(
id numeric(20) not null,
n numeric
);
create index idx_test_slave_n on test_slave(n);
analyze test_slave;
-- Wrong plan. At first idx_test_main_tms index is used.
explain
select *
from test_main
where tms between current_date and current_date + 1
and id in
(select id from test_slave where n = 5)
order by tms desc
limit 100;
-- Right plan with error. At first idx_test_slave_n index is used.
explain
select *
from test_main
where tms between current_date and current_date + '1 hour'::interval
and id in
(select id from test_slave where n = 5)
order by tms desc
limit 100;
The wrong plan goes from the big main table.
The right plan scans the slave empty table at the beginning.
But it has error:
"Limit (cost=56.82..57.07 rows=100 width=14)"
" -> Sort (cost=56.82..58.61 rows=718 width=14)"
" Sort Key: test_main.tms"
" -> Nested Loop (cost=12.97..29.37 rows=718 width=14)"
" -> HashAggregate (cost=12.67..12.69 rows=2 width=20)"
" Group Key: test_slave.id"
" -> Bitmap Heap Scan on test_slave (cost=4.19..12.66
rows=5 width=20)"
" Recheck Cond: (n = 5::numeric)"
" -> Bitmap Index Scan on idx_test_slave_n
(cost=0.00..4.19 rows=5 width=0)"
" Index Cond: (n = 5::numeric)"
" -> Index Scan using test_main_pkey on test_main
(cost=0.29..8.33 rows=1 width=14)"
" Index Cond: (id = test_slave.id)"
" Filter: ((tms >= ('now'::cstring)::date) AND (tms <=
(('now'::cstring)::date + '01:00:00'::interval)))"
The HashAggregate step returns 2 rows. It joins with the main table by using
primary unique index test_main_pkey.
The Nested Loop step has to return maximum 2 rows but we see rows=718. It is
incorrect calculation.
And therefore the Sort step is so long.
And on greater timestamp interval the right plan cost will be more the wrong
plan cost.
Also it is very strange that the record count is equal 5, but not 0 in step
(Bitmap Index Scan on idx_test_slave_n (cost=0.00..4.19 rows=5 width=0)).
Slave table is empty, but row count is equal 5.
Regards,
Nikolay.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #14438: Wrong row count in the join plan with unique index scan
2016-11-28 18:21 BUG #14438: Wrong row count in the join plan with unique index scan nikolay.nikitin@infowatch.com
@ 2016-11-29 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-30 08:36 ` Re: BUG #14438: Wrong row count in the join plan with unique index scan Nikitin Nikolay <Nikolay.Nikitin@infowatch.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2016-11-29 23:07 UTC (permalink / raw)
To: nikolay.nikitin@infowatch.com; +Cc: pgsql-bugs
nikolay.nikitin@infowatch.com writes:
> I execute select with "in" condition on the empty table.
> This select works with wrong plan.
> I think the wrong plan is chosen becose the postgres optimizer incorrectly
> calculates row count in the right plan.
I've pushed a patch that should improve matters for such cases.
Thanks for the report!
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #14438: Wrong row count in the join plan with unique index scan
2016-11-28 18:21 BUG #14438: Wrong row count in the join plan with unique index scan nikolay.nikitin@infowatch.com
2016-11-29 23:07 ` Re: BUG #14438: Wrong row count in the join plan with unique index scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-11-30 08:36 ` Nikitin Nikolay <Nikolay.Nikitin@infowatch.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nikitin Nikolay @ 2016-11-30 08:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs
Thank you very much.
I see two commits:
Improve eqjoinsel_semi's behavior for small inner relat...
and
Fix bogus handling of JOIN_UNIQUE_OUTER/INNER cases...
But in the second fix parallel function is fixed.
Now we use the 9.4 postgres version in which there is no parallel select execution.
Will these changes be in the following version 9.4.x?
Will they work effectively there?
Regards,
Nikolay.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, November 30, 2016 2:08 AM
To: Nikitin Nikolay
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14438: Wrong row count in the join plan with unique index scan
nikolay.nikitin@infowatch.com writes:
> I execute select with "in" condition on the empty table.
> This select works with wrong plan.
> I think the wrong plan is chosen becose the postgres optimizer incorrectly
> calculates row count in the right plan.
I've pushed a patch that should improve matters for such cases.
Thanks for the report!
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-11-30 08:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-28 18:21 BUG #14438: Wrong row count in the join plan with unique index scan nikolay.nikitin@infowatch.com
2016-11-29 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-30 08:36 ` Nikitin Nikolay <Nikolay.Nikitin@infowatch.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