Postgres Pro
Downloads
Home   >   mailing lists

Why so slow? - Mailing list pgsql-performance

From Alessandro Gagliardi
Subject Why so slow?
Date
Msg-id CAAB3BBLxs4ZzjTpsdNDRSxSXEN=shjOoB=Mwfojvqs+jQQRLrg@mail.gmail.com
Whole thread Raw
Responses Re: Why so slow?  (Steve Crawford <scrawford@pinpointresearch.com>)
Re: Why so slow?  (Ants Aasma <ants.aasma@eesti.ee>)
List pgsql-performance
Tree view
Comparing 
SELECT DISTINCT(user_id) FROM blocks JOIN seen_its USING (user_id) WHERE seen_its.created BETWEEN (now()::date - interval '8 days')::timestamp AND now()::date::timestamp
to
SELECT DISTINCT(user_id) FROM seen_its WHERE created BETWEEN (now()::date - interval '8 days')::timestamp AND now()::date::timestamp
the difference is 100x. 

Here are my tables:

CREATE TABLE seen_its (
  user_id character(24) NOT NULL,
  moment_id character(24) NOT NULL,
  created timestamp without time zone,
  inserted timestamp without time zone DEFAULT now(),
  CONSTRAINT seen_its_pkey PRIMARY KEY (user_id , moment_id )
) WITH ( OIDS=FALSE );

CREATE INDEX seen_its_created_idx  ON seen_its  USING btree  (created );

CREATE INDEX seen_its_user_id_idx  ON seen_its  USING btree  (user_id );

CREATE TABLE blocks (
  block_id character(24) NOT NULL,
  user_id character(24) NOT NULL,
  created timestamp with time zone,
  locale character varying,
  shared boolean,
  private boolean,
  moment_type character varying NOT NULL,
  user_agent character varying,
  inserted timestamp without time zone NOT NULL DEFAULT now(),
  networks character varying[],
  lnglat point,
  timezone character varying,
  geohash character varying(20),
  CONSTRAINT blocks_pkey PRIMARY KEY (block_id )
) WITH ( OIDS=FALSE );

CREATE INDEX blocks_created_at_timezone_idx  ON blocks  USING btree  (timezone(timezone::text, created) );

CREATE INDEX blocks_created_idx  ON blocks  USING btree  (created  DESC NULLS LAST);
CREATE INDEX blocks_geohash_idx  ON blocks  USING btree  (geohash );
CREATE INDEX blocks_timezone_idx  ON blocks  USING btree  (timezone );
CREATE INDEX blocks_user_id_idx  ON blocks  USING btree  (user_id );

My blocks table has about 17M rows in it. My seen_its table has 1.9M rows in it (though that is expected to grow into the billions). 

Here is the EXPLAIN: http://explain.depesz.com/s/ley

I'm using PostgreSQL 9.0.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit

My random_page_cost is 2 and yet it still insists on using Seq Scan on blocks.

Whenever I use my blocks table, this seems to happen. I'm not sure what's wrong. 

Any help would be much appreciated.

Thank you,
-Alessandro

pgsql-performance by date:

Previous
From: Markus Innerebner
Date:
Subject: Re: Optimizer is not choosing index
Next
From: Steve Crawford
Date:
Subject: Re: Why so slow?