From: Andrus <kobruleht2@hot.ee>
To: Scott Carey <scott@richrelevance.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
Date: Wed, 26 Nov 2008 21:24:48 +0200
Message-ID: <91652AA4F32448288A88CD62CF84B330@andrusnotebook> (raw)
In-Reply-To: <BDFBB77C9E07BE4A984DAAE981D19F961ACA1F175A@EXVMBX018-1.exch018.msoutlookonline.net>
References: <gg9pg5$1edv$1@news.hub.org>
<492A7591.3000700@archonet.com>
<C2F601D897194339A0D2327188C781D6@andrusnotebook>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA1F175A@EXVMBX018-1.exch018.msoutlookonline.net>
Scott,
>My first thought on the query where a pattern being faster than the query
>with an exact value is that the planner does not have good enough
>statistics on that column. Without looking at the explain plans further, I
>would suggest trying something simple. The fact that it is fasster on 8.3
>but slower on 8.1 may have to do with changes between versions, or may
>simply be due to luck in the statistics sampling.
>See if increasing the statistics target on that column significantly does
>anything:
>EXPLAIN (your query);
ALTER TABLE orders_products ALTER COLUMN product_id SET STATISTICS 2000;
ANALYZE orders_products;
EXPLAIN (your query);
>2000 is simply a guess of mine for a value much larger than the default.
>This will generally make query planning slower but the system will have a
>lot more data about that column and the distribution of data in it. This
>should help stabilize the query performance.
>If this has an effect, the query plans will change.
>Your question below really boils down to something more simple:
> --Why is the most optimal query plan not chosen? This is usually due to
> either insufficient statistics or quirks in how the query planner works on
> a specific data >set or with certain configuration options.
Thank you very much.
I found that AND dok.kuupaev = date'2008-11-21' runs fast but
AND dok.kuupaev BETWEEN date'2008-11-21' AND date'2008-11-21' runs very
slow.
explain SELECT sum(1)
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
WHERE rid.toode like '99000010%'
plan with default statistics:
"Aggregate (cost=17.86..17.87 rows=1 width=0)"
" -> Nested Loop (cost=0.00..17.85 rows=1 width=0)"
" -> Nested Loop (cost=0.00..11.84 rows=1 width=24)"
" Join Filter: ("outer".dokumnr = "inner".dokumnr)"
" -> Index Scan using dok_kuupaev_idx on dok (cost=0.00..5.81
rows=1 width=4)"
" Index Cond: ((kuupaev >= '2008-11-21'::date) AND
(kuupaev <= '2008-11-21'::date))"
" -> Index Scan using rid_toode_pattern_idx on rid
(cost=0.00..6.01 rows=1 width=28)"
" Index Cond: ((toode ~>=~ '99000010'::bpchar) AND (toode
~<~ '99000011'::bpchar))"
" Filter: (toode ~~ '99000010%'::text)"
" -> Index Scan using toode_pkey on toode (cost=0.00..6.00 rows=1
width=24)"
" Index Cond: ("outer".toode = toode.toode)"
after statistics is changed query runs fast ( 70 ... 1000 ms)
ALTER TABLE rid ALTER COLUMN toode SET STATISTICS 1000;
analyze rid;
explain analyze SELECT sum(1)
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
WHERE rid.toode like '99000010%'
AND dok.kuupaev BETWEEN date'2008-11-21' AND date'2008-11-21'
"Aggregate (cost=27.04..27.05 rows=1 width=0) (actual time=44.830..44.834
rows=1 loops=1)"
" -> Nested Loop (cost=0.00..27.04 rows=1 width=0) (actual
time=0.727..44.370 rows=108 loops=1)"
" -> Nested Loop (cost=0.00..21.02 rows=1 width=24) (actual
time=0.688..40.519 rows=108 loops=1)"
" -> Index Scan using dok_kuupaev_idx on dok (cost=0.00..5.81
rows=1 width=4) (actual time=0.027..8.094 rows=1678 loops=1)"
" Index Cond: ((kuupaev >= '2008-11-21'::date) AND
(kuupaev <= '2008-11-21'::date))"
" -> Index Scan using rid_dokumnr_idx on rid
(cost=0.00..15.20 rows=1 width=28) (actual time=0.011..0.011 rows=0
loops=1678)"
" Index Cond: ("outer".dokumnr = rid.dokumnr)"
" Filter: (toode ~~ '99000010%'::text)"
" -> Index Scan using toode_pkey on toode (cost=0.00..6.00 rows=1
width=24) (actual time=0.016..0.020 rows=1 loops=108)"
" Index Cond: ("outer".toode = toode.toode)"
"Total runtime: 45.050 ms"
It seems that you are genius.
I used 1000 since doc wrote that max value is 1000
Rid table contains 3.5millions rows, will increase 1 millions of rows per
year and is updated frequently, mostly by adding.
Is it OK to leave
SET STATISTICS 1000;
setting for this table this column or should I try to decrease it ?
Andrus.
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: kobruleht2@hot.ee, scott@richrelevance.com, dev@archonet.com
Subject: Re: Increasing pattern index query speed
In-Reply-To: <91652AA4F32448288A88CD62CF84B330@andrusnotebook>
* 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