From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-performance@postgresql.org
Cc: Daniel Fekete <dani@shopzeus.com>
Subject: Slow query on CLUTER -ed tables
Date: Wed, 23 Mar 2011 21:29:16 +0100
Message-ID: <4D8A581C.6040505@shopzeus.com> (raw)
Given two tables:
CREATE TABLE product_price_history
(
hid bigint NOT NULL,
hdate timestamp without time zone NOT NULL,
id bigint NOT NULL,
product_id bigint NOT NULL,
.... more columns here
CONSTRAINT pk_product_price_history PRIMARY KEY (hid);
CREATE INDEX idx_product_price_history_id_hdate
ON product_price_history
USING btree
(id, hdate);
CREATE TABLE product_price_offer_history
(
hid bigint NOT NULL,
product_price_id bigint NOT NULL,
isfeatured smallint NOT NULL,
price double precision NOT NULL,
shipping double precision NOT NULL,
.... some more coumns here
CONSTRAINT pk_product_price_offer_history PRIMARY KEY (hid, offerno)
);
Stats:
product_price_history - tablesize=23GB, indexes size=4GB, row count = 87
million
product_price_offer_history - tablesize=24GB, indexes size=7GB, row
count = 235 million
These tables store historical data of some million products from the
last year.
The following commands are executed on them daily:
CLUSTER idx_product_price_history_id_hdate on product_price_history;
CLUSTER pk_product_price_offer_history on product_price_offer_history;
Here is a query:
select
date_part('epoch', min(pph.hdate) ) as hdate_ticks,
min(ppoh.price+ppoh.shipping) as price_plus_shipping
from
product_price_history pph
inner join product_price_offer_history ppoh on ppoh.hid = pph.hid
where pph.id = 37632081
and ppoh.isfeatured=1
group by ppoh.merchantid,pph.hid,pph.hdate
order by pph.hid asc
I think that the query plan is correct:
"GroupAggregate (cost=5553554.25..5644888.17 rows=2283348 width=50)"
" -> Sort (cost=5553554.25..5559262.62 rows=2283348 width=50)"
" Sort Key: pph.hid, ppoh.merchantid, pph.hdate"
" -> Nested Loop (cost=0.00..5312401.66 rows=2283348 width=50)"
" -> Index Scan using idx_product_price_history_id_hdate
on product_price_history pph (cost=0.00..8279.80 rows=4588 width=16)"
" Index Cond: (id = 37632081)"
" -> Index Scan using pk_product_price_offer_history on
product_price_offer_history ppoh (cost=0.00..1149.86 rows=498 width=42)"
" Index Cond: (ppoh.hid = pph.hid)"
" Filter: (ppoh.isfeatured = 1)"
So it uses two index scans on the indexes we CLUSTER the tables on.
Number of rows returned is usually between 100 and 20 000.
Here is the problem. When I first open this query for a given
identifier, it runs for 100 seconds. When I try to run it again for the
same identifier it returns the same rows within one second!
The indexes are very well conditioned: from the 235 million rows, any id
given occurs at most 20 000 times. It is a btree index, so it should
already be stored sorted, and the 20 000 rows to be returned should fit
into a few database pages. Even if they are not in the cache, PostgreSQL
should be able to read the required pages within a second.
I understand that for an index scan, PostgreSQL also needs to read the
rows from the table. But since these tables are CLUSTER-ed on those
specific indexes, all the data needed shoud fit on a few database pages
and PostgreSQL should be able to read them within a second.
Then why it is taking 100 seconds to do the query for the first time and
why it is just one sec for the second time? Probably my thinking is
wrong, but I suppose it means that the data is spread on thousands of
pages on the disk.
How is that possible? What am I doing wrong?
Thanks,
Laszlo
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, dani@shopzeus.com
Subject: Re: Slow query on CLUTER -ed tables
In-Reply-To: <4D8A581C.6040505@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox