pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedClustering problem
3+ messages / 2 participants
[nested] [flat]
* Clustering problem
@ 2005-07-08 20:55 CG <cgg007@yahoo.com>
2005-07-08 23:24 ` Re: Clustering problem PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 3+ messages in thread
From: CG @ 2005-07-08 20:55 UTC (permalink / raw)
To: pgsql-sql
I have what I call a "dictionary" table which supports a "master table".
This dictionary table is designed to hold generic data :
CREATE TABLE sup_data (link_id uniqueidentifier, field_name varchar(255),
field_data text) WITH OIDS; ...
It works well when you're digging into it to pull the supplementary information
for a small number of rows in the master table. It uses an index on the
link_id, and can jump right to the few pages for the supplemental data. That
was the design.
Now "the powers that be" want to do some aggreate inquiries on subsets of the
generic data, based on many rows from the master table. This doesn't work so
well... Its having to pull many pages to create the result set to aggreate on.
If I could cluster the generic data to match the clustering on the "master
table" it would reduce the number of pulled pages considerably and the speedup
would make it work well.
I'm trying to avoid replicating the column and index used to cluster the main
table in this dictionary table.
Is it even possible to cluster a table based on the clustering scheme (which is
not the link_id ...) from the master table?
Can you gurus think of a better strategy? :) (Please??) :)
CG
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.
http://auctions.yahoo.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clustering problem
2005-07-08 20:55 Clustering problem CG <cgg007@yahoo.com>
@ 2005-07-08 23:24 ` PFC <lists@boutiquenumerique.com>
2005-07-11 18:02 ` Re: Clustering problem CG <cgg007@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: PFC @ 2005-07-08 23:24 UTC (permalink / raw)
To: pgsql-sql
> Is it even possible to cluster a table based on the clustering scheme
> (which is
> not the link_id ...) from the master table?
>
> Can you gurus think of a better strategy? :) (Please??) :)
You can create a functional index on a function which returns the desired
order by looking in the main table, cluster it, then drop the index...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clustering problem
2005-07-08 20:55 Clustering problem CG <cgg007@yahoo.com>
2005-07-08 23:24 ` Re: Clustering problem PFC <lists@boutiquenumerique.com>
@ 2005-07-11 18:02 ` CG <cgg007@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: CG @ 2005-07-11 18:02 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; pgsql-sql
Why would you then drop the index? Performance and storage issues? I imagine
that I would cluster the table at regular intervals to maintain the ordering,
so I'd need to to keep the index around, yes?
--- PFC <lists@boutiquenumerique.com> wrote:
>
>
> > Is it even possible to cluster a table based on the clustering scheme
> > (which is
> > not the link_id ...) from the master table?
> >
> > Can you gurus think of a better strategy? :) (Please??) :)
>
> You can create a functional index on a function which returns the desired
> order by looking in the main table, cluster it, then drop the index...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.
http://auctions.yahoo.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-11 18:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-08 20:55 Clustering problem CG <cgg007@yahoo.com>
2005-07-08 23:24 ` PFC <lists@boutiquenumerique.com>
2005-07-11 18:02 ` CG <cgg007@yahoo.com>
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