pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Queries running forever, because of wrong rowcount estimate
5+ messages / 2 participants
[nested] [flat]

* Queries running forever, because of wrong rowcount estimate
@ 2023-02-12 00:42  Peter <pmc@citylink.dinoex.sub.org>
  0 siblings, 0 replies; 5+ messages in thread

From: Peter @ 2023-02-12 00:42 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


TL;DR Version:
==============
For a table where all rows have been deleted, under certain conditions
the planner does assume that it contains the values that were
previousely present, and will therefore assume that nothing needs to
be added, while in fact everything needs to be added.
-----
This is
PostgreSQL 12.13 on amd64-portbld-freebsd13.1, compiled by FreeBSD
clang version 13.0.0 (git@github.com:llvm/llvm-project.git
llvmorg-13.0.0-0-gd7b669b3a303), 64-bit

and if somebody can tell me this has been fixed in a more recent
version, then I am happy.
-------------------------------------------------------

More elaborate Version:
=======================
When you have a table populated, pg_class may look like this:

 relpages | reltuples                                                                              
----------+-----------                                                                             
        1 |         3                                                                              

Now when you delete all rows, this will not change, and the planner
will now make the -wrong- assumption that the tuples are still
present, and planning may be bad. That is why ANALYZE is now
needed, and after ANALYZE things look like this, and do work again:

 relpages | reltuples 
----------+-----------
        1 |         0

But, if you happen to run a VACUUM ANALYZE on that table, then things
will look like this:

 relpages | reltuples 
----------+-----------
        0 |         0

And now, for reasons unknown to me, the planner will again make the
assupltion that all the previous values are still present in the
table, and planning will be bad. And at that point you can run
ANALYZE as often as you want, it does not help anymore.
-------------------------------------------------------

Full Version:
=============

Lets start with the query:

INSERT INTO opcode (opcode)
SELECT DISTINCT
       incoming.opcode AS opcode
  FROM incoming AS incoming
       LEFT JOIN opcode AS opcode USING (opcode)
 WHERE opcode.opcode IS NULL

Table "incoming" will bring millions of rows, but column "opcode"
knows only three distinct values. Others might probably appear, so
this INSERT will add such new value to table "opcode".

So far, no problem.

But what happens if I delete all rows from "opcode" and start the
process afresh? Then the INSERT *should* reinsert all the (three)
values freshly into table "opcode", i.e. there will be *millions*
of rows "WHERE opcode.opcode IS NULL", before the "DISTINCT".

This is also not a problem. Not yet.

Now we decide that not all the rows in table "incoming" are already
processable, so we limit the whole operation to those that are:

INSERT INTO opcode (opcode)
SELECT DISTINCT
       incoming.opcode AS opcode
  FROM incoming AS incoming
       LEFT JOIN opcode AS opcode USING (opcode)
 WHERE opcode.opcode IS NULL
   AND EXISTS (
     SELECT ready.id
       FROM tmp_ready AS ready
      WHERE ready.id = incoming.id
     )

And now this query will run forever and never return:

"Unique  (cost=434008.43..434008.44 rows=1 width=6)"
"  ->  Sort  (cost=434008.43..434008.44 rows=1 width=6)"
"        Sort Key: incoming.opcode"
"        ->  Nested Loop Semi Join  (cost=1058.15..434008.42 rows=1 width=6)"
"              Join Filter: (incoming.id = pairs.ori)"
"              ->  Gather  (cost=1058.15..151427.56 rows=1 width=14)"
"                    Workers Planned: 3"
"                    ->  Hash Anti Join  (cost=58.15..150427.46 rows=1 width=14)"
"                          Hash Cond: (incoming.opcode = opcode.opcode)"
"                          ->  Parallel Seq Scan on incoming  (cost=0.00..147589.32 rows=741332 width=14)"
"                          ->  Hash  (cost=31.40..31.40 rows=2140 width=6)"
"                                ->  Seq Scan on opcode  (cost=0.00..31.40 rows=2140 width=6)"
"              ->  Seq Scan on tmp_ready ready  (cost=0.00..253869.27 rows=2296927 width=8)"

The LEFT JOIN is estimated with only one result row (we know there can
actually be millions), and therefore this goes into a Nested Loop that
will read tmp_ready forever. 

Putting an Index onto tmp_ready helps the matter - but that is not the
root cause here.
The root cause is a wrong estimate: When deleting all rows from table
"opcode", this apparently does not change the planner behaviour. The
planner still thinks that all values from the incoming.opcode column are
already in opcode.opcode.

After trying with different experiments, I finally managed to somehow
kick ANALYZE to produce working statistics, and now it looks like this:

"HashAggregate  (cost=554927.43..554927.44 rows=1 width=6)"
"  Group Key: incoming.opcode"
"  ->  Hash Semi Join  (cost=291554.87..549213.83 rows=2285442 width=6)"
"        Hash Cond: (incoming.id = ready.id)"
"        ->  Hash Anti Join  (cost=1.01..192070.20 rows=2286638 width=14)"
"              Hash Cond: (incoming.opcode = opcode.opcode)"
"              ->  Seq Scan on incoming  (cost=0.00..163157.29 rows=2298129 width=14)"
"              ->  Hash  (cost=1.00..1.00 rows=1 width=6)"
"                    ->  Seq Scan on opcode  (cost=0.00..1.00 rows=1 width=6)"
"        ->  Hash  (cost=253869.27..253869.27 rows=2296927 width=8)"
"              ->  Seq Scan on tmp_ready ready  (cost=0.00..253869.27 rows=2296927 width=8)"

The LEFT JOIN has the correct rowcount now, and obviousely no nested
stuff is attempted with that.

There are a couple of approaches how to solve this, like
 - just put an index on tmp_ready
 - just disallow the loop: SET enable_nestloop = 'f' 

But that doesn't seem professional, as they don't tackle the very place of
the defect.
The question is: how to get ANALYZE to actually do it's work?


Let's investigate:

We have the original content table "opcode":

 id | opcode 
----+--------
 29 | NOTIFY
 30 | UPDATE
 31 | QUERY

And for certain the query works (and delivers 0 result rows):

"Unique  (cost=433951.35..433951.36 rows=1 width=6) (actual time=1938.400..1941.805 rows=0 loops=1)"
"  Buffers: shared hit=2694 read=137678"
"  ->  Sort  (cost=433951.35..433951.35 rows=1 width=6) (actual time=1938.399..1941.803 rows=0 loops=1)"
"        Sort Key: incoming.opcode"
"        Sort Method: quicksort  Memory: 25kB"
"        Buffers: shared hit=2694 read=137678"
"        ->  Nested Loop Semi Join  (cost=1001.07..433951.34 rows=1 width=6) (actual time=1938.387..1941.791 rows=0 loops=1)"
"              Join Filter: (incoming.id = pairs.ori)"
"              Buffers: shared hit=2694 read=137678"
"              ->  Gather  (cost=1001.07..151370.48 rows=1 width=14) (actual time=1938.386..1941.789 rows=0 loops=1)"
"                    Workers Planned: 3"
"                    Workers Launched: 3"
"                    Buffers: shared hit=2694 read=137678"
"                    ->  Hash Anti Join  (cost=1.07..150370.38 rows=1 width=14) (actual time=1928.520..1928.524 rows=0 loops=4)"
"                          Hash Cond: (incoming.opcode = opcode.opcode)"
"                          Buffers: shared hit=2694 read=137678"
"                          ->  Parallel Seq Scan on incoming  (cost=0.00..147589.32 rows=741332 width=14) (actual time=0.199..1651.435 rows=574232 loops=4)"
"                                Buffers: shared hit=2498 read=137678"
"                          ->  Hash  (cost=1.03..1.03 rows=3 width=6) (actual time=0.063..0.066 rows=3 loops=4)"
"                                Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                Buffers: shared hit=4"
"                                ->  Seq Scan on opcode  (cost=0.00..1.03 rows=3 width=6) (actual time=0.043..0.047 rows=3 loops=4)"
"                                      Buffers: shared hit=4"
"              ->  Seq Scan on tmp_pairs pairs  (cost=0.00..253869.27 rows=2296927 width=8) (never executed)"
"Planning Time: 0.512 ms"
"Execution Time: 1941.862 ms"

We have this data in pg_class and pg_stats:

 relpages | reltuples 
----------+-----------
        1 |         3

 attname | n_distinct | most_common_vals | most_common_freqs |   histogram_bounds    | correlation 
---------+------------+------------------+-------------------+-----------------------+-------------
 id      |         -1 |                  |                   | {29,30,31}            |           1
 opcode  |         -1 |                  |                   | {NOTIFY,QUERY,UPDATE} |         0.5


# delete from dnstap2.opcode;
DELETE 3

Stats have not changed, and the query planning is still the same,
which will, now, run forever.



# analyze dnstap2.opcode;
ANALYZE

 relpages | reltuples 
----------+-----------
        1 |         0

 attname | n_distinct | most_common_vals | most_common_freqs |   histogram_bounds    | correlation 
---------+------------+------------------+-------------------+-----------------------+-------------
 id      |         -1 |                  |                   | {29,30,31}            |           1
 opcode  |         -1 |                  |                   | {NOTIFY,QUERY,UPDATE} |         0.5

The planning has now changed, and the query works as intended.


Next table, same behaviour, so it also works.
But then, next table "proto" is empty, and here the query does run forever:

 relpages | reltuples 
----------+-----------
        0 |         0

 attname | n_distinct | most_common_vals | most_common_freqs |  histogram_bounds   | correlation 
---------+------------+------------------+-------------------+---------------------+-------------
 id      |         -1 |                  |                   | {37,38,39,40}       |           1
 proto   |         -1 |                  |                   | {TCP,TCP6,UDP,UDP6} |           1

# analyze dnstap2.proto;
ANALYZE

This does not change anything, and the query does still run forever!

Let's look closer:
"oid"		"what"		"nspname"	"relname"	"filename"	"relpages"
------------------------------------------------------------------------------------------
"9625390"	"SELF"		"dnstap2"	"proto"		"9625390"	0
"9625394"	"TOAST"		"dnstap2"	"proto"		"9625394"	0
"9625396"	"TOASTIDX"	"dnstap2"	"proto"		"9625396"	1
"9625399"	"proto_uniq"	"dnstap2"	"proto"		"9625399"	2

# dir 962539[0469]*
-rw-------  1 770  770      0 Feb 11 05:37 9625390
-rw-------  1 770  770  16384 Feb 11 05:41 9625390_fsm
-rw-------  1 770  770      0 Feb 11 05:37 9625390_vm
-rw-------  1 770  770      0 Feb  7 11:59 9625394
-rw-------  1 770  770   8192 Feb  8 04:53 9625396
-rw-------  1 770  770  16384 Feb 11 05:41 9625399

At 05:37 this was the nightly VACUUM ANALYZE (there is no autovacuum
here).

Next table: "addr"

 relpages | reltuples 
----------+-----------
        0 |         0

[stats are lenghty]

And it does not work.

Next table: "status"

 relpages | reltuples 
----------+-----------
        0 |         0

 attname | n_distinct | most_common_vals | most_common_freqs |                   histogram_bounds                    | correlation 
---------+------------+------------------+-------------------+-------------------------------------------------------+-------------
 id      |         -1 |                  |                   | {55,56,57,58,59,60}                                   |           1
 status  |         -1 |                  |                   | {BADCOOKIE,FORMERR,NOERROR,NXDOMAIN,REFUSED,SERVFAIL} |           1

And it does not work.


Okay, lets grab the "opcode" table and see further...

# VACUUM ANALYZE dnstap2.opcode;
VACUUM

 relpages | reltuples 
----------+-----------
        0 |         0

KAPUTT! Query runs now forever.

# ANALYZE dnstap2.opcode;
ANALYZE

Nada, query runs forever.

# insert into dnstap2.opcode (opcode) Values ('crap');
INSERT 0 1

 relpages | reltuples 
----------+-----------
        0 |         0



# analyze dnstap2.opcode;
ANALYZE

 relpages | reltuples 
----------+-----------
        1 |         1

Too bad, query runs forever. :(

 attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation 
---------+------------+------------------+-------------------+------------------+-------------
 id      |         -1 |                  |                   |                  |            
 opcode  |         -1 |                  |                   |                  |            

Ups. When did that happen?

# vacuum analyze dnstap2.opcode;
VACUUM

# analyze dnstap2.opcode;
ANALYZE

# delete from dnstap2.opcode;
DELETE 1

# analyze dnstap2.opcode;
ANALYZE

 relpages | reltuples 
----------+-----------
        1 |         0

 attname | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation 
---------+------------+------------------+-------------------+------------------+-------------
 id      |         -1 |                  |                   |                  |            
 opcode  |         -1 |                  |                   |                  |            

Okay, my query works again!


Friends, I am not clear on WHAT exactly goes wrong here, but my gut
feeling is very strong that something does go really wrong here.

What exactly do I need to do after deleting all rows from tables?
ANALYZE obviousely.
What else?
VACUUM, apparently, absolutely NOT.

But what if it runs interim from cron schedule?
Then I would need to manually insert some values into the concerned
tables. run ANALYZE, delete the values again, run ANALYZE, and only
then operations could resume. This cannot be the idea.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* [Testcase] Queries running forever, because of wrong rowcount estimate
@ 2023-02-13 05:33  Peter <pmc@citylink.dinoex.sub.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Peter @ 2023-02-13 05:33 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

CREATE DATABASE ttc
    WITH
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'de_DE.UTF-8'
    LC_CTYPE = 'de_DE.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

select version();
PostgreSQL 12.13 on amd64-portbld-freebsd13.1, compiled by FreeBSD clang version 13.0.0 (git@github.com:llvm/llvm-project.git llvmorg-13.0.0-0-gd7b669b3a303), 64-bit


CREATE TABLE  public.incoming
(
    id bigint NOT NULL,
    data text COLLATE pg_catalog."default",
    CONSTRAINT incoming_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;

insert into incoming(id) select generate_series(1,1000000);
update incoming set data = 'EINS' where data IS NULL;
insert into incoming(id) select generate_series(1000001,2000000);
update incoming set data = 'ZWEI' where data IS NULL;
insert into incoming(id) select generate_series(2000001,3000000);
update incoming set data = 'DREI' where data IS NULL;

CREATE TABLE IF NOT EXISTS public.data
(
    data text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT data_pkey PRIMARY KEY (data)
)
TABLESPACE pg_default;

insert into data (data) values ('EINS'), ('DREI');

analyze public.data;
analyze public.incoming;

explain
select distinct data
from incoming 
Left outer join public.data using (data)
where data.data is null;

 HashAggregate  (cost=67371.04..67371.07 rows=3 width=5)
   Group Key: incoming.data
   ->  Hash Anti Join  (cost=0.55..64871.04 rows=1000000 width=5)
         Hash Cond: (incoming.data = data.data)
         ->  Seq Scan on incoming  (cost=0.00..44745.50 rows=3000000 width=5)
         ->  Hash  (cost=0.52..0.52 rows=2 width=5)
               ->  Seq Scan on data  (cost=0.00..0.52 rows=2 width=5)

delete from data;
vacuum analyze data;

explain
select distinct data
from incoming 
Left outer join public.data using (data)
where data.data is null;

 Unique  (cost=56056.08..56056.09 rows=1 width=5)
   ->  Sort  (cost=56056.08..56056.09 rows=1 width=5)
         Sort Key: incoming.data
         ->  Hash Anti Join  (cost=60.58..56056.07 rows=1 width=5)
               Hash Cond: (incoming.data = data.data)
               ->  Seq Scan on incoming  (cost=0.00..44745.50 rows=3000000 width=5)
               ->  Hash  (cost=29.70..29.70 rows=2470 width=5)
                     ->  Seq Scan on data  (cost=0.00..29.70 rows=2470 width=5)

"rows=1" in the "Hash Anti Join" line is WRONG. It should be
3000000. Or at least some thousands.
On the next-higher level there will now a Nested Loop chosen. And that
Nested Loop will do whatever costly things it needs to do - only not 1
time but three million times.

I think I have a workaround patch also.

-------------------------------------------------------------------
Server Configuration Tuning:

 
< #port = 5432                          # (change requires restart)
< #max_connections = 100                        # (change requires restart)
---
> port = 5434                           # (change requires restart)
> max_connections = 60                  # (change requires restart)

< #shared_buffers = 32MB                        # min 128kB
---
> shared_buffers = 40MB                 # min 128kB

< #temp_buffers = 8MB                   # min 800kB
---
> temp_buffers = 20MB                   # min 800kB

< #work_mem = 4MB                               # min 64kB
< #maintenance_work_mem = 64MB          # min 1MB
---
> work_mem = 50MB                       # min 64kB
> maintenance_work_mem = 50MB           # min 1MB

< #max_stack_depth = 2MB                        # min 100kB
---
> max_stack_depth = 40MB                        # min 100kB

< #max_files_per_process = 1000         # min 25
---
> max_files_per_process = 200           # min 25

< #effective_io_concurrency = 1         # 1-1000; 0 disables prefetching
---
> effective_io_concurrency = 5          # 1-1000; 0 disables prefetching

< #max_parallel_workers_per_gather = 2  # taken from max_parallel_workers
---
> max_parallel_workers_per_gather = 0   # taken from max_parallel_workers

< #synchronous_commit = on              # synchronization level;
---
> synchronous_commit = off              # synchronization level;

< #wal_sync_method = fsync              # the default is the first option
---
> wal_sync_method = fsync               # the default is the first option

< #full_page_writes = on                        # recover from partial page writes
---
> full_page_writes = off                        # recover from partial page writes

< #wal_init_zero = on                   # zero-fill new WAL files
---
> wal_init_zero = off                   # zero-fill new WAL files

< #wal_writer_delay = 200ms             # 1-10000 milliseconds
---
> wal_writer_delay = 2000ms             # 1-10000 milliseconds

< #checkpoint_timeout = 5min            # range 30s-1d
---
> checkpoint_timeout = 10min            # range 30s-1d

< #checkpoint_completion_target = 0.5   # checkpoint target duration, 0.0 - 1.0
---
> checkpoint_completion_target = 0.0    # checkpoint target duration, 0.0 - 1.0

< #seq_page_cost = 1.0                  # measured on an arbitrary scale
< #random_page_cost = 4.0                       # same scale as above
---
> seq_page_cost = 0.5                   # measured on an arbitrary scale
> random_page_cost = 0.7                        # same scale as above / PMc: SSD

< #effective_cache_size = 4GB
---
> effective_cache_size = 1GB

< update_process_title = off
---
> update_process_title = on

< #autovacuum = on                      # Enable autovacuum subprocess?  'on'
---
> autovacuum = off                      # Enable autovacuum subprocess?  'on' 

< #datestyle = 'iso, mdy'
---
> datestyle = 'german, dmy'

< #timezone = 'GMT'
---
> timezone = 'Europe/Berlin'

< #lc_messages = 'C'                    # locale for system error message
---
> lc_messages = 'en_US.UTF-8'                   # locale for system error message

< #lc_monetary = 'C'                    # locale for monetary formatting
< #lc_numeric = 'C'                     # locale for number formatting
< #lc_time = 'C'                                # locale for time formatting
---
> lc_monetary = 'en_US.UTF-8'                   # locale for monetary formatting
> lc_numeric = 'en_US.UTF-8'                    # locale for number formatting
> lc_time = 'de_DE.UTF-8'                               # locale for time formatting

< #default_text_search_config = 'pg_catalog.simple'
---
> default_text_search_config = 'pg_catalog.german'





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [Testcase] Queries running forever, because of wrong rowcount estimate
@ 2023-02-13 17:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter <pmc@citylink.dinoex.sub.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Tom Lane @ 2023-02-13 17:38 UTC (permalink / raw)
  To: Peter <pmc@citylink.dinoex.sub.org>; +Cc: pgsql-general@lists.postgresql.org

Peter <pmc@citylink.dinoex.sub.org> writes:
> "rows=1" in the "Hash Anti Join" line is WRONG. It should be
> 3000000. Or at least some thousands.

FWIW, this behaves better in v14 and up.  In older versions there's
an ambiguity about what relpages=reltuples=0 means; it's the state
when ANALYZE has never yet examined the table contents, but it's
also the state when we have found the table to be empty.  Because
of the former case, older planner versions don't like to believe
that a table is empty even if reltuples says so.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [Testcase] Queries running forever, because of wrong rowcount estimate
@ 2023-02-13 18:18  Peter <pmc@citylink.dinoex.sub.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Peter @ 2023-02-13 18:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

On Mon, Feb 13, 2023 at 12:38:12PM -0500, Tom Lane wrote:
! Peter <pmc@citylink.dinoex.sub.org> writes:
! > "rows=1" in the "Hash Anti Join" line is WRONG. It should be
! > 3000000. Or at least some thousands.
! 
! FWIW, this behaves better in v14 and up.  In older versions there's
! an ambiguity about what relpages=reltuples=0 means; it's the state
! when ANALYZE has never yet examined the table contents, but it's
! also the state when we have found the table to be empty.  Because
! of the former case, older planner versions don't like to believe
! that a table is empty even if reltuples says so.

Thanks, Tom, I found that as change 3d351d916b2 - that should
make the issue more unlikely and not so prone to happen during
testing.
OTOH, that code did apparently not do anything evil that would
not be possible to happen under other circumstances. It's unlikely,
because one would at some point change such queries to a more
performant style, but I am currently trying to understand why
it can happen at all...

cheers,
PMc





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* [Outcome] Queries running forever, because of wrong rowcount estimate
@ 2023-02-14 18:38  Peter <pmc@citylink.dinoex.sub.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Peter @ 2023-02-14 18:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

On Mon, Feb 13, 2023 at 12:38:12PM -0500, Tom Lane wrote:
! Peter <pmc@citylink.dinoex.sub.org> writes:
! > "rows=1" in the "Hash Anti Join" line is WRONG. It should be
! > 3000000. Or at least some thousands.
! 
! FWIW, this behaves better in v14 and up.  In older versions there's
! an ambiguity about what relpages=reltuples=0 means; it's the state
! when ANALYZE has never yet examined the table contents, but it's
! also the state when we have found the table to be empty.  Because
! of the former case, older planner versions don't like to believe
! that a table is empty even if reltuples says so.
! 
! 			regards, tom lane

Okay, I got someway through with it.

Given, the destination table is empty, and VACUUMed. Then there is
relpages = reltuples = 0.

We do some
"source LEFT OUTER JOIN destination ON (matchfield) WHERE matchfield IS NULL"
The source is big, but carries only a few distinct matchfield values.

The so-called "hack" in heapam_handler.c:heapam_estimate_rel_size()
does then makes the planner believe that there are actually 10
curpages. This is not wrong, because the same might happen if one does
an INSERT amounting for 10 pages and not yet ANALYZE.

We then get a calculated rowcount of whatever, lets assume 2330 rows.

Then we go into eqjoinsel():

n_distinct for the left source table is known, it is (lets assume) 4.
n_distinct for the right destination table is not known because it is
not analyzed, but it is UNIQUE, so it becomes 2330.

We get into eqjoinsel_inner(): 

MCV (right) for the destination is not known because it is not analyzed.

selec = selec / nd2   => 0.000429

We get into eqjoinsel_semi():

                        if (nd1 <= nd2 || nd2 < 0)
                                selec = 1.0 - nullfrac1;

Voila, selec = 1.0

And so the ANTI JOIN will estimate to 0 (aka 1) result rows, instead
of whatever rowcount the source brings along (usually big).

----------------- cut -----------------------
Okay, so this should be fixed in R.14.

But, as mentioned above, the same can also happen in normal operations

Example:
I have a logfile from my webserver and a table to collect the (unique)
IP-addresses. I start with an empty table and feed in the log.

First round, the destination addresses. Lats assume there are 2330
different addresses.
Second round, the source addresses. There are only 4 different ones.

I don't do an ANALYZE between the two steps.

And voila, I get the same effect as above. (Been there, done
it: duration: 30216273.583 ms)

Agreed, this is an inefficient way of doing that. It would be better
to do a UNION on the source data, at that point filter it accordingly,
then DISTINCT, and only then the ANTI JOIN. 

But I'm a lazy guy and I may not care if a query that could run in
0.5 seconds takes 60 seconds instead.
However, it's not so nice when it takes 10 hours.

Anyway, I don't get a real clue on what this stance should actually
achieve (from selfuncs.c:eqjoinsel_semi()#2773):

			if (nd1 <= nd2 || nd2 < 0)
				selec = 1.0 - nullfrac1;
			else
				selec = (nd2 / nd1) * (1.0 - nullfrac1);

This seems to assume a 100% match whenever the left (source) table
brings fewer distinct(!) values than the right (destination) table
already carries.

For now, I have just disabled this behaviour, in the rough&dirty way:

inline patch 1: src/backend/optimizer/path/costsize.c (+3/-0 lines) download inline-1.patch--- src/backend/optimizer/path/costsize.c.orig 2022-11-07 22:47:13.000000000 +0100 +++ src/backend/optimizer/path/costsize.c 2023-02-13 00:04:54.156489000 +0100 @@ -4685,8 +4685,11 @@ /* pselec not used */ break; case JOIN_ANTI: + if(fkselec * jselec > 0.9975) { + jselec = 0.9975; + } nrows = outer_rows * (1.0 - fkselec * jselec); nrows *= pselec; break; default: /* other values not expected here */
cheerio, PMc ^ permalink raw reply [nested|flat] 5+ messages in thread


end of thread, other threads:[~2023-02-14 18:38 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-12 00:42 Queries running forever, because of wrong rowcount estimate Peter <pmc@citylink.dinoex.sub.org>
2023-02-13 05:33 [Testcase] Queries running forever, because of wrong rowcount estimate Peter <pmc@citylink.dinoex.sub.org>
2023-02-13 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 18:18   ` Peter <pmc@citylink.dinoex.sub.org>
2023-02-14 18:38   ` Peter <pmc@citylink.dinoex.sub.org>

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