From: | Alessandro Gagliardi <alessandro(at)path(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Why so slow? |
Date: | 2012-02-17 18:34:50 |
Message-ID: | CAAB3BBLxs4ZzjTpsdNDRSxSXEN=shjOoB=Mwfojvqs+jQQRLrg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2012-02-17 19:21:47 | Re: Why so slow? |
Previous Message | Markus Innerebner | 2012-02-16 06:54:37 | Re: Optimizer is not choosing index |