...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] De la part de Reg
Me Please
Envoyé : mardi 30 décembre 2008 17:09
À : Scott Marlowe
Cc : Scott Ribe; Gauthier, Dave; pgsql-general@postgresql.org
Objet : Re: [GENERAL] [PGSQL 8.3.5] Use of a partial indexes
Here it comes:
-- DDL
Only one question remains in my mind:
why the planner is not using the partial index?
The partial index is covering 2 predicates out of the 3 used in the where
condition. Actually there is a boolean flag (to exclude disabled rows),
a timestamp (for row age) and an int8 (a FK to another table).
On Tue, Dec 30, 2008 at 2:02 AM, Reg Me Please regmeple...@gmail.com wrote:
Only one question remains in my mind:
why the planner is not using the partial index?
The partial index is covering 2 predicates out of the 3 used in the where
condition. Actually there is a boolean flag (to exclude
Reg Me Please wrote:
Only one question remains in my mind:
why the planner is not using the partial index?
The partial index is covering 2 predicates out of the 3 used in the where
condition. Actually there is a boolean flag (to exclude disabled rows),
a timestamp (for row age) and an int8
Here it comes:
-- DDL
CREATE TABLE gm_t_movimenti_magazzini
(
gm_movi_unic INT8 NOT NULL REFERENCES gm_t_movimenti,
gm_moti_unic TEXT NOT NULL REFERENCES gm_t_movimenti_tipi,
ap_prod_unic INT8 NOT NULL REFERENCES ap_t_prodotti,
gm_maga_unic TEXT NOT NULL REFERENCES gm_t_magazzini,
] [PGSQL 8.3.5] Use of a partial indexes
Here it comes:
-- DDL
CREATE TABLE gm_t_movimenti_magazzini
(
gm_movi_unic INT8 NOT NULL REFERENCES gm_t_movimenti,
gm_moti_unic TEXT NOT NULL REFERENCES gm_t_movimenti_tipi,
ap_prod_unic INT8 NOT NULL REFERENCES ap_t_prodotti,
gm_maga_unic
HI all.
I have a 8M+ rows table over which I run a query with a and-only WHERE
condition.
The table has been periodically VACUUMed and ANALYZEd.
In the attempt of speeding that up I added a partial index in order to limit
the size of the index. Of course that index is modeled after a slowly
returns.
Good Luck !
-dave
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Reg Me Please
Sent: Monday, December 29, 2008 9:09 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] [PGSQL 8.3.5] Use of a partial indexes
8.3.5] Use of a partial indexes
HI all.
I have a 8M+ rows table over which I run a query with a and-only WHERE
condition.
The table has been periodically VACUUMed and ANALYZEd.
In the attempt of speeding that up I added a partial index in order to
limit the size of the index. Of course
On Mon, Dec 29, 2008 at 7:41 AM, Reg Me Please regmeple...@gmail.com wrote:
Hi.
The WHERE condition can be divided into a slowly changing part and in
a random one. The random part is the one I change at every query to avoid
result caching.
The planner seems to be smart enough to learn while
The WHERE condition can be divided into a slowly changing part and in
a random one. The random part is the one I change at every query to avoid
result caching.
The first query will leave in cache at least many of the index pages needed
by the second query, and likely actual rows needed by the
On Mon, Dec 29, 2008 at 8:36 AM, Scott Ribe scott_r...@killerbytes.com wrote:
Creating the partial index reads rows, and the pages are left in the disk
cache. The only way to do proper comparisons is to reboot between trials in
order to compare queries with cold caches, or use the latter of
voila! cache dumped.
What about read caches in the disk devices themselves?
--
Scott Ribe
scott_r...@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On Mon, Dec 29, 2008 at 9:28 AM, Scott Ribe scott_r...@killerbytes.com wrote:
voila! cache dumped.
What about read caches in the disk devices themselves?
Given that most drives have caches that are in the 16 to 32Meg range,
I doubt it makes a big difference. But you can always just dd a file
14 matches
Mail list logo