Hi Andy,

Yeah sorry about the long name, there are all generated by function as part of 
a table versioning system. And yes I placed all 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=111041 width=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=43331 width=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

-----Original Message-----
From: Andy Colson [mailto:a...@squeakycode.net] 
Sent: Monday, 17 January 2011 5:22 p.m.
To: Jeremy Palmer
Cc: pgsql-performance@postgresql.org
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 more than 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 test box so it should be ok.  If its 
live, you could wrap it in a BEGIN ... ROLLBACK (in theory... never tried it 
myself)

-Andy
______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to 
legal privilege. 
If you are not the intended recipient, you must not peruse, use, disseminate, 
distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 
0800 665 463 or i...@linz.govt.nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any 
attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to