title: Postgres optimize query index based on aggregates
body: <p>I have the following Query/View: </p>
  
  <pre><code>CREATE OR REPLACE VIEW "SumAndSalesPerCountryYear" AS 
   SELECT date_part('year'::text, "Invoice"."InvoiceDate") AS year,
      "Invoice"."BillingCountry" AS country,
      sum("Invoice"."Total") AS total
     FROM "Invoice"
    GROUP BY date_part('year'::text, "Invoice"."InvoiceDate"), "Invoice"."BillingCountry"
    ORDER BY date_part('year'::text, "Invoice"."InvoiceDate") DESC, sum("Invoice"."Total") DESC;
  </code></pre>
  
  <p>My table structure is as follows:</p>
  
  <pre><code>CREATE TABLE "Invoice"
  (
    "InvoiceId" integer NOT NULL,
    "CustomerId" integer NOT NULL,
    "InvoiceDate" timestamp without time zone NOT NULL,
    "BillingAddress" character varying(70),
    "BillingCity" character varying(40),
    "BillingState" character varying(40),
    "BillingCountry" character varying(40),
    "BillingPostalCode" character varying(10),
    "Total" numeric(10,2) NOT NULL,
    CONSTRAINT "PK_Invoice" PRIMARY KEY ("InvoiceId"),
    CONSTRAINT "FK_InvoiceCustomerId" FOREIGN KEY ("CustomerId")
        REFERENCES "Customer" ("CustomerId") MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  )
  WITH (
    OIDS=FALSE
  );
  </code></pre>
  
  <p>The current execution plan is </p>
  
  <pre><code>Sort  (cost=33.65..34.54 rows=354 width=21) (actual time=0.691..0.698 rows=101 loops=1)"
    Sort Key: (date_part('year'::text, "Invoice"."InvoiceDate")), (sum("Invoice"."Total"))
    Sort Method: quicksort  Memory: 32kB
    -&gt;  HashAggregate  (cost=14.24..18.67 rows=354 width=21) (actual time=0.540..0.567 rows=101 loops=1)
          -&gt;  Seq Scan on "Invoice"  (cost=0.00..11.15 rows=412 width=21) (actual time=0.015..0.216 rows=412 loops=1)
  Total runtime: 0.753 ms
  </code></pre>
  
  <p>My task is to optimize the query by using indices, however i cannot think of a way to use indices for optimizing aggregate results.</p>