Forum » Izdelava spletišč » pgSQL problem z indexi...
pgSQL problem z indexi...
ADF ::
Na dveh tabelah ne moreš narediti indexa (v Oraclu9i lahko - join bitmap index). Zato naredi dva indeksa:
CREATE INDEX prvi_index ON d (col1)
CREATE INDEX drugi_index ON m (col2)
CREATE INDEX prvi_index ON d (col1)
CREATE INDEX drugi_index ON m (col2)
Primoz ::
Teh dveh indexov ti pgsql skoraj zagotovo ne bo uporabu... precej več koristi boš naredil, če poindeksiraš tabelo d po polju po katerem joinas ter po col1, tabelo m pa po polju po katerem joinas.
poglej si explain analyze svojega querya, da boš videl kakšen query plan ti je pripravil planer.
poglej si explain analyze svojega querya, da boš videl kakšen query plan ti je pripravil planer.
There can be no real freedom without the freedom to fail.
Kostko ::
@Primoz: tudi če ustvarim indexe tako, jih query planner ne uporabi. še kakšna ideja ?
kaj mi javi analyze za query:
EXPLAIN ANALYZE SELECT p.*, m.col1, u.col1 FROM table1 p LEFT JOIN table2 u ON u.colx = p.coly LEFT JOIN table3 m ON m.colid = p.colfoo WHERE p.colbar = 'Foo' ORDER BY p.col1 DESC, m.col2 OFFSET 0 LIMIT 30;
kaj mi javi analyze za query:
EXPLAIN ANALYZE SELECT p.*, m.col1, u.col1 FROM table1 p LEFT JOIN table2 u ON u.colx = p.coly LEFT JOIN table3 m ON m.colid = p.colfoo WHERE p.colbar = 'Foo' ORDER BY p.col1 DESC, m.col2 OFFSET 0 LIMIT 30;
Limit (cost=10280.31..10280.38 rows=30 width=161) (actual time=1644.065..1644.091 rows=30 loops=1)
-> Sort (cost=10280.31..10306.85 rows=10616 width=161) (actual time=1644.058..1644.071 rows=30 loops=1)
Sort Key: p.col1, m.col2
-> Hash Left Join (cost=4927.10..9067.89 rows=10616 width=161) (actual time=234.656..1107.495 rows=10442 loops=1)
Hash Cond: ("outer".col1 = "inner".colid)
-> Hash Left Join (cost=530.54..3740.85 rows=10616 width=137) (actual time=40.162..362.143 rows=10442 loops=1)
Hash Cond: ("outer".coly = "inner".colx)
-> Seq Scan on table1 p (cost=0.00..2470.93 rows=10615 width=127) (actual time=2.424..171.931 rows=10442 loops=1)
Filter: ((colbar)::text = 'Foo'::text)
-> Hash (cost=409.63..409.63 rows=16363 width=14) (actual time=37.546..37.546 rows=0 loops=1)
-> Seq Scan on table2 u (cost=0.00..409.63 rows=16363 width=14) (actual time=0.057..22.645 rows=16363 loops=1)
-> Hash (cost=4137.65..4137.65 rows=29165 width=28) (actual time=188.520..188.520 rows=0 loops=1)
-> Seq Scan on table3 m (cost=0.00..4137.65 rows=29165 width=28) (actual time=0.067..155.464 rows=29165 loops=1) Human stupidity is not convergent, it has no limit!
Primoz ::
daj mi se \d tabela1
\d tabela2
pa \d tabela3 pokaz
pa pozen vacuum full;
\d tabela2
pa \d tabela3 pokaz
pa pozen vacuum full;
There can be no real freedom without the freedom to fail.
Kostko ::
Evo zadeva je tlele:
http://www.unimatrix-one.org/pg-stuff.txt
http://www.unimatrix-one.org/pg-stuff.txt
Human stupidity is not convergent, it has no limit!
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | SQL vprasanje (strani: 1 2 )Oddelek: Programiranje | 7869 (4548) | BivšiUser2 |
| » | [VB] datagridviewOddelek: Programiranje | 1833 (1216) | korenje3 |
| » | c# datagridview in comboxOddelek: Programiranje | 1130 (1018) | OmegaM |
| » | postgreSql/Mysql - počasen countOddelek: Programiranje | 1182 (1103) | yimi |
| » | MySQL združevanje tabel..Oddelek: Programiranje | 1628 (1443) | Nemenej |