Thread: ALTER TABLE tabla ALTER COLUMN columna SET STATISTICS number
¿where is stored the value set by ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS = [1-1000]? I've set this to 1000, and I didn't remember in which column (doh!). Is there any table to look? (I did 'grep "set stat" $PGDATA/pg_log/*' and found it, but may be there is a better way) I couldn't find it in the docs neithr "googling" Greetings -------------------------------------- Long life, little spam and prosperity
On Wed, Jul 06, 2005 at 04:49:21PM -0300, Dario wrote: > where is stored the value set by ALTER TABLE table_name ALTER COLUMN > column_name SET STATISTICS = [1-1000]? pg_attribute.attstattarget Example query: SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget > 0; See the "System Catalogs" chapter in the documentation for more information. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
(first at all, sorry for my english)
Hi.
- Does "left join" restrict the order in which the planner must join
tables? I've read about join, but i'm not sure about left join...
- If so: Can I avoid this behavior? I mean, make the planner resolve the
query, using statistics (uniqueness, data distribution) rather than join
order.
My query looks like:
SELECT ...
FROM a, b,
LEFT JOIN c ON (c.key = a.key)
LEFT JOIN d on (d.key=a.key)
WHERE (a.key = b.key) AND (b.column <= 100)
b.column has a lot better selectivity, but planner insist on resolve
first c.key = a.key.
Of course, I could rewrite something like:
SELECT ...
FROM
(SELECT ...
FROM a,b
LEFT JOIN d on (d.key=a.key)
WHERE (b.column <= 100)
)
as aa
LEFT JOIN c ON (c.key = aa.key)
but this is query is constructed by an application with a "multicolumn"
filter. It's dynamic.
It means that a user could choose to look for "c.column = 1000". And
also, combinations of filters.
So, I need the planner to choose the best plan...
I've already change statistics, I clustered tables with cluster, ran vacuum
analyze, changed work_mem, shared_buffers...
Greetings. TIA.
Dario Pudlo wrote: > (first at all, sorry for my english) > Hi. > - Does "left join" restrict the order in which the planner must join > tables? I've read about join, but i'm not sure about left join... > - If so: Can I avoid this behavior? I mean, make the planner resolve the > query, using statistics (uniqueness, data distribution) rather than join > order. > > My query looks like: > SELECT ... > FROM a, b, > LEFT JOIN c ON (c.key = a.key) > LEFT JOIN d on (d.key=a.key) > WHERE (a.key = b.key) AND (b.column <= 100) > > b.column has a lot better selectivity, but planner insist on resolve > first c.key = a.key. > > Of course, I could rewrite something like: > SELECT ... > FROM > (SELECT ... > FROM a,b > LEFT JOIN d on (d.key=a.key) > WHERE (b.column <= 100) > ) > as aa > LEFT JOIN c ON (c.key = aa.key) > > but this is query is constructed by an application with a "multicolumn" > filter. It's dynamic. > It means that a user could choose to look for "c.column = 1000". And > also, combinations of filters. > > So, I need the planner to choose the best plan... Probably forcing the other join earlier could help: SELECT ... FROM a JOIN b ON (a.key = b.key) LEFT JOIN c ON (c.key = a.key) ... I think the problem is that postgresql can't break JOIN syntax very easily. But you can make the JOIN earlier. John =:-> > > I've already change statistics, I clustered tables with cluster, ran vacuum > analyze, changed work_mem, shared_buffers... > > Greetings. TIA. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Attachment
On Wed, Jul 06, 2005 at 18:54:02 -0300, Dario Pudlo <dariop@unitech.com.ar> wrote: > (first at all, sorry for my english) > Hi. > - Does "left join" restrict the order in which the planner must join > tables? I've read about join, but i'm not sure about left join... The left join operator is not associative so in general the planner doesn't have much flexibility to reorder left (or right) joins.