CODEWITHSUNDEEP
postgresqlquery-optimizationdatabase-performance
Eli_Rozen
Eli_Rozen

Reputation: 1301

Postgresql very slow query

This query runs very slow. Why? Others are fine. Indexes are good, I think.

explain analyze 
select "e_inst"."si_id" as "c0" 
from "e_inst" as "e_inst" 
group by "e_inst"."si_id" 
order by "e_inst"."si_id" ASC NULLS LAST

Query Plan:

Sort  (cost=12221.87..12221.90 rows=68 width=4) (actual time=1115.377..1115.433 rows=81 loops=1)
  Sort Key: si_id
  Sort Method: quicksort  Memory: 28kB
  ->  HashAggregate  (cost=12221.25..12221.45 rows=68 width=4) (actual time=1115.198..1115.261 rows=81 loops=1)
        ->  Seq Scan on e_inst  (cost=0.00..11920.07 rows=602357 width=4) (actual time=0.021..611.570 rows=602357 loops=1)
Total runtime: 1115.538 ms

Create table and indexes:

CREATE TABLE e_inst (
    id integer NOT NULL,
    ip numeric,
    gu character varying,
    referrer character varying,
    proc integer,
    loke_id integer,
    top_id integer,
    si_id integer,
    kop integer,
    count integer,
    created integer,
    modified integer,
    timepop integer,
    count_active character varying,
    country character(3),
    info character varying
);

CREATE INDEX "topEnhance" ON e_inst USING btree (created, top_id);
CREATE INDEX "procEnhance" ON e_inst USING btree (created, proc);
CREATE INDEX "countryEnhance" ON e_install USING btree (created, country);
CREATE INDEX "createdE" ON e_inst USING btree (created);
ALTER TABLE e_inst CLUSTER ON "createdE";
CREATE INDEX "lokeE" ON e_inst USING btree (loke_id);
CREATE INDEX "lokeEnhance" ON e_inst USING btree (created, loke_id);
CREATE INDEX "siE" ON e_inst USING btree (si_id);
CREATE INDEX "siEnhance" ON e_inst USING btree (created, si_id);
CREATE INDEX "kopEnhance" ON e_inst USING btree (created, kop);

Upvotes: 1

Views: 2667

Answers (2)

Eli_Rozen
Eli_Rozen

Reputation: 1301

Upgraded to PostgreSQL 9.2. That's now an index only scan! Works good, thanks for a_horse_with_no_name who advised me to upgrade.

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26454

Indexes aren't going to be used by a query which processes the whole table.

The fact is you are retrieving and processing 600k records. That it does this in just over a second is actually kind of impressive.

Now in this case, you are trying to pull out the 81 distinct values from the 600k records. what you may want to do is to construct a recursive query such that it fetches one row 81 times. This may be faster but there is no guarantee. Normally I use these where there are far fewer rows returned. However here is an example:

WITH RECURSIVE sparse_scan AS (
    SELECT min(si_id) as si_id FROM e_inst
    UNION ALL
    SELECT min(si_id) as si_id
      FROM e_inst
      JOIN (select max(si_id) as last FROM sparse_scan) s
     WHERE s.last < si_id
)
SELECT si_id as c0 FROM sparse_scan;

Note that this replaces a sequential scan with 81 index scans.

Upvotes: 3

Related Questions