Search Postgresql Archives

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

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

 



But Jeff said "left open indefinitely without either committing or rolling back".  Your process is committing the transaction.

On 1/2/19 6:15 AM, Mark wrote:
Hi Jeff, 

Thanks for your help. That is exactly what is happening.

I have a long running job which deletes all of the common_student table and then repopulates it. It takes long time to load all the other data and commit the transaction. I didn't think the delete inside the transaction would have any effect until it is commited or rolled back.

I will have to rewrite the application so it updates the existing rows rather than deleting all and then inserting.

Thanks again for helping me understand what's happening here. 

Proof:

db=> explain analyze select * from common_student where school_id = 36;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_student  (cost=88.30..3846.49 rows=1533 width=384) (actual time=4.852..7.065 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..87.91 rows=1533 width=0) (actual time=4.817..4.817 rows=1388 loops=1)
         Index Cond: (school_id = 36)
 Planning time: 0.097 ms
 Execution time: 8.084 ms
(7 rows)

db=> /* At this point I have started a long running transaction that deletes all of common_student for school_id 36  */ ;

db=> analyse verbose common_student(school_id); 
INFO:  analyzing "public.common_student"
INFO:  "common_student": scanned 7322 of 7322 pages, containing 65431 live rows and 8060 dead rows; 56818 rows in sample, 65431 estimated total rows
ANALYZE
db=> explain analyze select * from common_student where school_id = 36;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_common_student_sid on common_student  (cost=0.41..8.43 rows=1 width=384) (actual time=0.017..1.498 rows=1388 loops=1)
   Index Cond: (school_id = 36)
 Planning time: 0.098 ms
 Execution time: 2.583 ms
(4 rows)

db=> /* At this point I have killed the long running transaction that deletes all of common_student for school_id 36  */ ;
db=> vacuum analyze common_student; 
VACUUM
db=> explain analyze select * from common_student where school_id = 36;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on common_student  (cost=79.17..3357.79 rows=1388 width=383) (actual time=0.088..1.302 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..78.83 rows=1388 width=0) (actual time=0.077..0.077 rows=1388 loops=1)
         Index Cond: (school_id = 36)
 Planning time: 0.327 ms
 Execution time: 2.311 ms
(7 rows)


On Sun, 23 Dec 2018 at 02:57 Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

- Does the analyse output below mean that it only scanned 51538 of 65463 rows in the table? Is school_id 36 just being missed in the sample? (This happens when the analyse is repeated )

Is there a transaction which had deleted all of school_id=36, and then was just left open indefinitely without either committing or rolling back?

That would explain it, and I don't know of anything else that could.  The deleted but not committed tuples are still live, but don't get sampled.

Cheers,

Jeff

--
Angular momentum makes the world go 'round.

[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