Re: Possible to improve query plan?

От: Andy Colson
Тема: Re: Possible to improve query plan?
Дата: ,
Msg-id: 4D33CC39.1020600@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: Possible to improve query plan?  (Jeremy Palmer)
Ответы: Re: Possible to improve query plan?  (Jeremy Palmer)
Список: pgsql-performance

Скрыть дерево обсуждения

Possible to improve query plan?  (Jeremy Palmer, )
 Re: Possible to improve query plan?  (Andy Colson, )
  Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )
 Re: Possible to improve query plan?  (Jayadevan M, )
 Re: Possible to improve query plan?  (Tom Lane, )
  Re: Possible to improve query plan?  (Jeremy Palmer, )
  Re: Possible to improve query plan?  (Mladen Gogala, )

> -----Original Message-----
> From: Andy Colson [mailto:]
> Sent: Monday, 17 January 2011 5:22 p.m.
> To: Jeremy Palmer
> Cc: 
> Subject: Re: [PERFORM] Possible to improve query plan?
>
>
> First, wow, those are long names... I had a hard time keeping track.
>
> Second: you have lots of duplicated indexes. I count _revision_created in 4 indexes?  Not sure what other sql you are
using,but have you tried one index for one column?  PG will be able to Bitmap them together if it thinks it can use
morethan one.  Was that because you were testing? 
>
> Third: any chance we can get an "explain analyze"?  It give's more info.  (Also, have you seen
http://explain.depesz.com/)
>
> Last: If you wanted to force the index usage, for a test, you could drop the other indexes.  I assume this is on a
testbox so it should be ok.  If its live, you could wrap it in a BEGIN ... ROLLBACK (in theory... never tried it
myself)
>
> -Andy

On 01/16/2011 10:43 PM, Jeremy Palmer wrote:
> Hi Andy,
>
> Yeah sorry about the long name, there are all generated by function as part of a table versioning system. And yes I
placedall possible indexes on the table to see which would be used by the planner. In production I will drop the unused
indexes.
>
> Yes simple drop the extra index :P I have dropped the index and it made the query slower :(
>
> Here is the explain analyse:
>
> Subquery Scan t  (cost=170692.25..175678.27 rows=767 width=205) (actual time=13762.783..14322.315 rows=106299
loops=1)'
>    Filter: (t.row_number = 1)'
>    ->   WindowAgg  (cost=170692.25..173760.57 rows=153416 width=86) (actual time=13762.774..14208.522 rows=149557
loops=1)'
>          ->   Sort  (cost=170692.25..171075.79 rows=153416 width=86) (actual time=13762.745..13828.584 rows=149557
loops=1)'
>                Sort Key: version_crs_coordinate_revision.id, version_crs_coordinate_revision._revision_created'
>                Sort Method:  quicksort  Memory: 23960kB
>                ->   Bitmap Heap Scan on version_crs_coordinate_revision  (cost=3319.13..157477.69 rows=153416
width=86)(actual time=70.925..13531.720 rows=149557 loops=1) 
>                      Recheck Cond: (((_revision_expired>  16) AND (_revision_expired<= 40)) OR ((_revision_created>
16)AND (_revision_created<= 40))) 
>                      Filter: (((_revision_created<= 16) AND (_revision_expired>  16) AND (_revision_expired<= 40)) OR
((_revision_created> 16) AND (_revision_created<= 40))) 
>                      ->   BitmapOr  (cost=3319.13..3319.13 rows=154372 width=0) (actual time=53.650..53.650 rows=0
loops=1)
>                            ->   Bitmap Index Scan on idx_crs_coordinate_revision_expired  (cost=0.00..2331.76
rows=111041width=0) (actual time=37.773..37.773 rows=110326 loops=1) 
>                                  Index Cond: ((_revision_expired>  16) AND (_revision_expired<= 40))
>                            ->   Bitmap Index Scan on idx_crs_coordinate_revision_created  (cost=0.00..910.66
rows=43331width=0) (actual time=15.872..15.872 rows=43258 loops=1) 
>                                  Index Cond: ((_revision_created>  16) AND (_revision_created<= 40))
> Total runtime: 14359.747 ms
>
> http://explain.depesz.com/s/qpL says that the bitmap heap scan is bad. Not sure what to do about it.
>
> Thanks,
> Jeremy
>


Hum.. yeah it looks like it takes no time at all to pull data from the individual indexes, and them bitmap them.  I'm
notsure what the bitmap heap scan is, or why its slow.  Hopefully someone smarter will come along. 

Also its weird that explain.depesz.com didnt parse and show your entire plan.  Hum.. you seem to have ending quotes on
someof the lines? 

One other though: quicksort  Memory: 23960kB
It needs 20Meg to sort... It could be your sort is swapping to disk.

What sort of PG version is this?
What are you using for work_mem?  (you could try to bump it up a little (its possible to set for session only, no need
forserver restart) and see if that'd help. 

And sorry, but its my bedtime, good luck though.

-Andy



В списке pgsql-performance по дате сообщения:

От: Jayadevan M
Дата:
Сообщение: Re: Possible to improve query plan?
От: "Ing. Marcos Ortiz Valmaseda"
Дата:
Сообщение: Re: Possible to improve query plan?