From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2 |
Date: | 2019-01-02 14:04:26 |
Message-ID: | 9e353edc-b04e-8273-f0b4-23bbd586fed1@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(dot)janes(at)gmail(dot)com
> <mailto:jeff(dot)janes(at)gmail(dot)com>> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mark | 2019-01-02 15:28:02 | Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2 |
Previous Message | Mark | 2019-01-02 12:27:18 | Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2 |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2019-01-02 15:02:21 | Re: chained transactions |
Previous Message | Peter Eisentraut | 2019-01-02 14:03:19 | Re: [PATCH] check for ctags utility in make_ctags |