Re: Possible to improve query plan?

On 01/16/2011 09:21 PM, Jeremy Palmer wrote:
> Hi all,
>
> I've come to a dead end in trying to get a commonly used query to perform better. The query is against one table with
10million rows. This table has been analysed. The table definition is: 
>
> CREATE TABLE version_crs_coordinate_revision
> (
>    _revision_created integer NOT NULL,
>    _revision_expired integer,
>    id integer NOT NULL,
>    cos_id integer NOT NULL,
>    nod_id integer NOT NULL,
>    ort_type_1 character varying(4),
>    ort_type_2 character varying(4),
>    ort_type_3 character varying(4),
>    status character varying(4) NOT NULL,
>    sdc_status character(1) NOT NULL,
>    source character varying(4),
>    value1 numeric(22,12),
>    value2 numeric(22,12),
>    value3 numeric(22,12),
>    wrk_id_created integer,
>    cor_id integer,
>    audit_id integer NOT NULL,
>    CONSTRAINT pkey_version_crs_coordinate_revision PRIMARY KEY (_revision_created, id),
>    CONSTRAINT version_crs_coordinate_revision_revision_created_fkey FOREIGN KEY (_revision_created)
>        REFERENCES revision (id) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION,
>    CONSTRAINT version_crs_coordinate_revision_revision_expired_fkey FOREIGN KEY (_revision_expired)
>        REFERENCES revision (id) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
>    OIDS=FALSE
> );
> ALTER TABLE "version".version_crs_coordinate_revision ALTER COLUMN _revision_created SET STATISTICS 1000;
> ALTER TABLE "version".version_crs_coordinate_revision ALTER COLUMN _revision_expired SET STATISTICS 1000;
> ALTER TABLE "version".version_crs_coordinate_revision ALTER COLUMN id SET STATISTICS 1000;
>
> CREATE INDEX idx_crs_coordinate_revision_created ON "version".version_crs_coordinate_revision USING btree
(_revision_created);
> CREATE INDEX idx_crs_coordinate_revision_created_expired ON "version".version_crs_coordinate_revision USING btree
(_revision_created,_revision_expired); 
> CREATE INDEX idx_crs_coordinate_revision_expired ON "version".version_crs_coordinate_revision USING btree
(_revision_expired);
> CREATE INDEX idx_crs_coordinate_revision_expired_created ON "version".version_crs_coordinate_revision USING btree
(_revision_expired,_revision_created); 
> CREATE INDEX idx_crs_coordinate_revision_expired_id ON "version".version_crs_coordinate_revision USING btree
(_revision_expired,id); 
> CREATE INDEX idx_crs_coordinate_revision_id ON "version".version_crs_coordinate_revision USING btree (id);
> CREATE INDEX idx_crs_coordinate_revision_id_created ON "version".version_crs_coordinate_revision USING btree (id,
_revision_created);
>
>
> The distribution of the data is that all but 120,000 rows have null values in the _revision_expired column.
>
> The query itself that I'm trying to optimise is below:
>
> EXPLAIN
> SELECT * FROM (
>      SELECT
>          row_number() OVER (PARTITION BY id ORDER BY _revision_created DESC) as row_number,
>          *
>      FROM
>          version_crs_coordinate_revision
>      WHERE (
>          (_revision_created<= 16 AND _revision_expired>  16 AND _revision_expired<= 40) OR
>          (_revision_created>  16 AND _revision_created<= 40)
>      )
> ) AS T
> WHERE row_number = 1;
>
> Subquery Scan t  (cost=170692.25..175678.27 rows=767 width=205)
>    Filter: (t.row_number = 1)
>    ->   WindowAgg  (cost=170692.25..173760.57 rows=153416 width=86)
>          ->   Sort  (cost=170692.25..171075.79 rows=153416 width=86)
>                Sort Key: version_crs_coordinate_revision.id, version_crs_coordinate_revision._revision_created
>                ->   Bitmap Heap Scan on version_crs_coordinate_revision  (cost=3319.13..157477.69 rows=153416
width=86)
>                      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)
>                            ->   Bitmap Index Scan on idx_crs_coordinate_revision_expired  (cost=0.00..2331.76
rows=111041width=0) 
>                                  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) 
>                                  Index Cond: ((_revision_created>  16) AND (_revision_created<= 40))
>
>
> One thought I have is that maybe the idx_crs_coordinate_revision_expired_created index could be used instead of
idx_crs_coordinate_revision_expired.
>
> Does anyone have any suggestions what I could do to improve the plan? Or how I could force the use of the
idx_crs_coordinate_revision_expired_createdindex to see if that is better. 
>
> Thanks
> Jeremy

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 test
boxso it should be ok.  If its live, you could wrap it in a BEGIN ... ROLLBACK (in theory... never tried it myself) 

-Andy