Обсуждение: Explain out put

Поиск
Список
Период
Сортировка

Explain out put

От
Yambu
Дата:
Hello

I suspect that the partial index is not correct.

It should include both column play_positionand elapsed. may you please help me figure out what's wrong here

Re: Explain out put

От
Jayson Hreczuck
Дата:
I hate to be "that guy", but my day doesn't go by without Yambu in my inbox!

On Tue, Jul 20, 2021 at 2:46 PM Yambu <hyambu@gmail.com> wrote:
Hello

I suspect that the partial index is not correct.

It should include both column play_positionand elapsed. may you please help me figure out what's wrong here



--


Jayson Hreczuck | Principal Database Administrator

C: +1 617.571.9861130 Turner St, Ste 530, Waltham, MA 02453

Re: Explain out put

От
Bruce Momjian
Дата:
On Tue, Jul 20, 2021 at 03:02:56PM -0400, Jayson Hreczuck wrote:
> I hate to be "that guy", but my day doesn't go by without Yambu in my inbox!

Yes, it is getting unreasonable and I am trying to ignore his emails.  I
hate to block them completely, but I am getting close to that.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.




Re: Explain out put

От
Vijaykumar Jain
Дата:
On Wed, 21 Jul 2021 at 00:16, Yambu <hyambu@gmail.com> wrote:
Hello

I suspect that the partial index is not correct.

It should include both column play_positionand elapsed. may you please help me figure out what's wrong here


I am not great at query tuning, but the estimates for playback_telemetry_2021_* partitions in bitmap scans look way off.
Can you run a vacuum (verbose,analyze) playback_telemetry  and check if the new plan is any better?


--
Thanks,
Vijay
Mumbai, India

Re: Explain out put

От
"David G. Johnston"
Дата:
On Tuesday, July 20, 2021, Yambu <hyambu@gmail.com> wrote:
Hello

I suspect that the partial index is not correct.

It should include both column play_positionand elapsed. may you please help me figure out what's wrong here


Not easily, but I do see the scan of an index with “partial” in its name so it is getting used.  Though the fact it thinks its returning 6,000 or so rows but in actuality gets almost 500,000 (each on two partitions) is a bit worrying (though maybe not solveable…and since you do have nearly 1,000,000 rows to deal with 3 seconds doesn’t seem terrible).  It doesn’t want to do 1,500 nested loops from the media_contents table onto an only 15,000 rows telemetry result.  I don’t know if it would think differently if it knew the inner result is 1 million instead (you could experiment with the planner GUCs).  I don’t know enough about the statistics to give concrete help on improving this other than make sure tou’ve run analyze on the table.

You may find keeping client_id on the telemetry table to be helpful if you need better performance.

All that said, I’m somewhat learning by teaching here so take this with a critical mindset.

I take it the query used to use aggregates?  If not the group by in the main query is just noise.  Also, as you are grouping by account_id in the CTE it will be impossible for rows to exist that the “select distinct” will get rid of.

HTH

David J.


Re: Explain out put

От
Yambu
Дата:
Thanks for everybody's help, but from below sent email records it doesnt seem to be like everyday,  18 emails sent since April,  thats 4 emails per month on average.

image.png

On Tue, Jul 20, 2021 at 9:09 PM Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Jul 20, 2021 at 03:02:56PM -0400, Jayson Hreczuck wrote:
> I hate to be "that guy", but my day doesn't go by without Yambu in my inbox!

Yes, it is getting unreasonable and I am trying to ignore his emails.  I
hate to block them completely, but I am getting close to that.

--
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Вложения

Re: Explain out put

От
"David G. Johnston"
Дата:
On Tue, Jul 20, 2021 at 6:09 PM Yambu <hyambu@gmail.com> wrote:
Thanks for everybody's help, but from below sent email records it doesnt seem to be like everyday,  18 emails sent since April,  thats 4 emails per month on average.

The impression that you quickly resort to asking questions on these lists instead of taking 5-10 minutes to search for answers is a real one regardless of the data.  Part of that has to do with how the questions are asked (and the general kinds of questions), in particular, without any indication that you tried something you thought should work and confusion as to why it didn't.  Somewhat counter-intuitively, being brief in your asking of questions works OK for the first few but quickly is interpreted as a lack of respect for others' time when done over and over again.  If a specific question is simple enough to ask in 10-15 words it likely has a readily searched for answer.

My $0.02

David J.

Re: Explain out put

От
Yambu
Дата:
Ok, thank you for your reply, well understood

Please accept my apologies

On Wed, Jul 21, 2021 at 3:19 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jul 20, 2021 at 6:09 PM Yambu <hyambu@gmail.com> wrote:
Thanks for everybody's help, but from below sent email records it doesnt seem to be like everyday,  18 emails sent since April,  thats 4 emails per month on average.

The impression that you quickly resort to asking questions on these lists instead of taking 5-10 minutes to search for answers is a real one regardless of the data.  Part of that has to do with how the questions are asked (and the general kinds of questions), in particular, without any indication that you tried something you thought should work and confusion as to why it didn't.  Somewhat counter-intuitively, being brief in your asking of questions works OK for the first few but quickly is interpreted as a lack of respect for others' time when done over and over again.  If a specific question is simple enough to ask in 10-15 words it likely has a readily searched for answer.

My $0.02

David J.

Re: Explain out put

От
Vijaykumar Jain
Дата:


On Wed, Jul 21, 2021, 6:53 AM Yambu <hyambu@gmail.com> wrote:
Ok, thank you for your reply, well understood

Please accept my apologies

I am not sure you should be the one doing that.
All you asked were pg related questions and it is infact a general question mailing list.
I do not feel it is right to do name calling like this in the entire public mailing list. I would have ignored the replies as well, but I am only replying so that no one feels intimidated and stop asking questions due to those replies.

Why is it so hard to ignore a query if it is so basic. let others who have enough free time, ( Yes I have no job so all the free  time n the world I keep looking for queries, discussions on the telegram ,  irc  and mailing list and if I can learn something new while trying to help someone. I consider it win win)

Keep asking without fear.

Re: Explain out put

От
Yambu
Дата:
Thank you Vijay

On Wed, Jul 21, 2021 at 6:40 AM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


On Wed, Jul 21, 2021, 6:53 AM Yambu <hyambu@gmail.com> wrote:
Ok, thank you for your reply, well understood

Please accept my apologies

I am not sure you should be the one doing that.
All you asked were pg related questions and it is infact a general question mailing list.
I do not feel it is right to do name calling like this in the entire public mailing list. I would have ignored the replies as well, but I am only replying so that no one feels intimidated and stop asking questions due to those replies.

Why is it so hard to ignore a query if it is so basic. let others who have enough free time, ( Yes I have no job so all the free  time n the world I keep looking for queries, discussions on the telegram ,  irc  and mailing list and if I can learn something new while trying to help someone. I consider it win win)

Keep asking without fear.

Re: Explain out put

От
Laurenz Albe
Дата:
On Wed, 2021-07-21 at 10:10 +0530, Vijaykumar Jain wrote:
> I am not sure you should be the one doing that.
> All you asked were pg related questions and it is infact a general question mailing list.
> I do not feel it is right to do name calling like this in the entire public mailing list.

I think it is ok to give Yambu feedback about his questions.

On the other hand, he hasn't broken any rules or spammed the list, so there
is no need for him to apologize (but perhaps he can spend a few minutes of
research before asking his next question).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: Explain out put

От
Yambu
Дата:
Thanks Laurenz,

Thank you all, you have helped me. I have learnt a few leasons

On Wed, Jul 21, 2021 at 8:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2021-07-21 at 10:10 +0530, Vijaykumar Jain wrote:
> I am not sure you should be the one doing that.
> All you asked were pg related questions and it is infact a general question mailing list.
> I do not feel it is right to do name calling like this in the entire public mailing list.

I think it is ok to give Yambu feedback about his questions.

On the other hand, he hasn't broken any rules or spammed the list, so there
is no need for him to apologize (but perhaps he can spend a few minutes of
research before asking his next question).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Re: Explain out put

От
Yambu
Дата:
I thought i might in turn help someone by sharing the lessons learned

Research before asking (did this , but going to do more)
Use fake name in case you are attacked in public forums
Do not be afraid to ask
No name calling in public forums (I don't do this by the way)
Offer constructive criticism (feedback on questions asked is important, it doesn't have to be harsh, just a pointer to right direction is good enough)

We are all trying to make the world a better one .

Thanks once again
regards




On Wed, Jul 21, 2021 at 8:09 AM Yambu <hyambu@gmail.com> wrote:
Thanks Laurenz,

Thank you all, you have helped me. I have learnt a few leasons

On Wed, Jul 21, 2021 at 8:00 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2021-07-21 at 10:10 +0530, Vijaykumar Jain wrote:
> I am not sure you should be the one doing that.
> All you asked were pg related questions and it is infact a general question mailing list.
> I do not feel it is right to do name calling like this in the entire public mailing list.

I think it is ok to give Yambu feedback about his questions.

On the other hand, he hasn't broken any rules or spammed the list, so there
is no need for him to apologize (but perhaps he can spend a few minutes of
research before asking his next question).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Re: Explain out put

От
Bruce Momjian
Дата:
On Wed, Jul 21, 2021 at 08:09:07AM +0200, Yambu wrote:
> Thanks Laurenz,
> 
> Thank you all, you have helped me. I have learnt a few leasons

I think the point is that there are thousands of people reading each
email, so it is reasonable to request people to do some research before
posting.  Yes, people who don't do research can be ignored, but each
reader needs to discover who does this, and if too many people do this,
the work of ignoring people becomes unacceptable and people stop reading
completely.

Also, people might find IRC and Slack to be better forms for quick
questions and to get more immediate feedback --- I use them often.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.




Re: Explain out put

От
Yambu
Дата:
Thank your,sir, now we are building one another , helpful tips indeed

On Wed, Jul 21, 2021 at 5:34 PM Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Jul 21, 2021 at 08:09:07AM +0200, Yambu wrote:
> Thanks Laurenz,
>
> Thank you all, you have helped me. I have learnt a few leasons

I think the point is that there are thousands of people reading each
email, so it is reasonable to request people to do some research before
posting.  Yes, people who don't do research can be ignored, but each
reader needs to discover who does this, and if too many people do this,
the work of ignoring people becomes unacceptable and people stop reading
completely.

Also, people might find IRC and Slack to be better forms for quick
questions and to get more immediate feedback --- I use them often.

--
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.