0

I've got a slow query which I want to optimize.

SELECT
    pand.bouwjaar AS construction_date,
    count(*)
FROM pand AS pand
WHERE 
    pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp without time zone 
    AND (pand.einddatumtijdvakgeldigheid IS NULL OR pand.einddatumtijdvakgeldigheid >= 'now'::text::timestamp without time zone) 
    AND pand.aanduidingrecordinactief = false 
    AND pand.geom_valid = true 
    AND pand.pandstatus <> 'Niet gerealiseerd pand'::pandstatus 
    AND pand.pandstatus <> 'Pand gesloopt'::pandstatus

GROUP BY pand.bouwjaar 

At the moment it takes 37999.326 ms while it's 'just' a group by and count on a indexed column. I know the query is hard, it has to process over 10 million rows, but i hope the query can do better then 38 seconds.

This is the Explain(analyze, buffers):

HashAggregate  (cost=1087761.16..1087762.90 rows=174 width=5) (actual time=37998.961..37999.173 rows=855 loops=1)
  Buffers: shared hit=5312 read=606349
  ->  Seq Scan on pand  (cost=0.00..1037569.41 rows=10038351 width=5) (actual time=1.237..31246.011 rows=9935732 loops=1)
        Filter: ((NOT aanduidingrecordinactief) AND geom_valid AND (pandstatus <> 'Niet gerealiseerd pand'::pandstatus) AND (pandstatus <> 'Pand gesloopt'::pandstatus) AND (begindatumtijdvakgeldigheid <= ('now'::cstring)::timestamp without time zone) AND ( (...)
        Rows Removed by Filter: 4261215
        Buffers: shared hit=5312 read=606349
Total runtime: 37999.326 ms

If I'm ready this Explain-message correct my biggest problem lies with the HasAggregate with the high Cost.

The strange thing is I already have an index on pand.bouwjaar. How can I speed up this query? Or should I deal with it ;)

When removing the whole Where clause I'm getting the following queryplan:

HashAggregate  (cost=824615.21..824617.66 rows=245 width=5) (actual time=23589.184..23589.612 rows=1033 loops=1)
  Buffers: shared hit=5473 read=606188
  ->  Seq Scan on pand  (cost=0.00..753630.47 rows=14196947 width=5) (actual time=2.528..13207.042 rows=14196947 loops=1)
        Buffers: shared hit=5473 read=606188
Total runtime: 23589.862 ms

Still 23.6 seconds :(

Update

As requested the information on the table and version info:

PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

CREATE TABLE pand
(
  gid serial NOT NULL,
  identificatie numeric(16,0),
  aanduidingrecordinactief boolean,
  aanduidingrecordcorrectie integer,
  officieel boolean,
  inonderzoek boolean,
  begindatumtijdvakgeldigheid timestamp without time zone,
  einddatumtijdvakgeldigheid timestamp without time zone,
  documentnummer character varying(20),
  documentdatum date,
  pandstatus pandstatus,
  bouwjaar numeric(4,0),
  geom_valid boolean,
  geovlak geometry,
  CONSTRAINT pand_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_geometrie CHECK (st_ndims(geovlak) = 3),
  CONSTRAINT enforce_geotype_geometrie CHECK (geometrytype(geovlak) = 'POLYGON'::text OR geovlak IS NULL),
  CONSTRAINT enforce_srid_geometrie CHECK (st_srid(geovlak) = 28992)
)
WITH (
  OIDS=TRUE
);

Indexes on this table:

CREATE INDEX pand_key
  ON pand
  USING btree
  (identificatie, aanduidingrecordinactief, aanduidingrecordcorrectie, begindatumtijdvakgeldigheid);


CREATE INDEX pand_idx_bouwjaar
  ON pand
  USING btree
  (bouwjaar);

CREATE INDEX pand_geom_idx
  ON pand
  USING gist
  (geovlak);
7
  • The majority of the time is spent in reading the values (31 seconds for the Seq Scan), the hash aggregates only adds 8 seconds on top of that. You might want to try an index on begindatumtijdvakgeldigheid if the condition for that column reduces the number of rows substantially.
    – user1822
    Apr 2, 2015 at 12:57
  • Hmm creating an index on that column reduces the query with 3 seconds :( Even if I remove the complete where clause the query is still 23.6 seconds. See the query plan above.
    – stUrb
    Apr 2, 2015 at 13:06
  • Removing the where clause should make it slower, not faster as it now processes more rows. I guess the reduced time is more caused by file system caching rather than the removal of the where clause.
    – user1822
    Apr 2, 2015 at 13:08
  • Hmm that's a pitty. So there isn't a way to speed this up?
    – stUrb
    Apr 2, 2015 at 13:36
  • Well, checking the numbers, the sequential scan returns 9935732 rows, with the filter excluding the remaining 4261215. This means that 2/3 of your table matches the WHERE condition - I would not expect any index helping much on this. Apr 2, 2015 at 14:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.