'-06-01' order by id_record desc limit 1;
mam ma?y problem z partycjonowaniem.
Zapytanie bezposrednio na tabeli dziecku uzywa indexu
EXPLAIN ANALYZE SELECT id_record from cdrs_06 where start_time QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.07 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1) -> Index Scan Backward using cdrs_06_pk on cdrs_06 (cost=0.00..92.28 rows48 width=8) (actual time=0.027..0.027 rows=1 loops=1) Filter: (start_time > '-06-01 '::timestamp without time zone) Total runtime: 0.046 ms (4 rows)
ale juz zapytanie na "mamusi" uzywa na dziecku seqscana
EXPLAIN ANALYZE SELECT id_record from cdrs where start_time > '-06-01' order by id_record desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost2.04..12.05 rows=1 width=8) (actual time155.076..5.077 rows=1 loops=1) -> Sort (cost2.04..92.45 rows61 width=8) (actual time155.074..5.074 rows=1 loops=1) Sort Key: public.cdrs.id_record Sort Method: top-N heapsort Memory: 25kB -> Result (cost=0.00..51.24 rows61 width=8) (actual time=5.992..8.274 rows18 loops=1) -> Append (cost=0.00..51.24 rows61 width=8) (actual time=5.991..7.506 rows18 loops=1) -> Seq Scan on cdrs (cost=0.00..12.75 rowss width=8) (actual time=0.001..0.001 rows=0 loops=1) Filter: (start_time > '-06-01 '::timestamp without time zone) -> Seq Scan on cdrs_06 cdrs (cost=0.00..12.99 rows42 width=8) (actual time=5.989...525 rows18 loops=1) Filter: (start_time > '-06-01 '::timestamp without time zone)
mozna jakos wymusic uzycie indexu w takim przypadku ?
postgres 8.3.1
Postgres partycjonowanie i uzycie indeksu
ale juz zapytanie na "mamusi" uzywa na dziecku seqscana
EXPLAIN ANALYZE SELECT id_record from cdrs where start_time '-06-01' order by id_record desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost2.04..12.05 rows=1 width=8) (actual time155.076..5.077 rows=1 loops=1) - Sort (cost2.04..92.45 rows61 width=8) (actual time155.074..5.074 rows=1 loops=1) Sort Key: public.cdrs.id_record Sort Method: top-N heapsort Memory: 25kB - Result (cost=0.00..51.24 rows61 width=8) (actual time=5.992..8.274 rows18 loops=1) - Append (cost=0.00..51.24 rows61 width=8) (actual time=5.991..7.506 rows18 loops=1) - Seq Scan on cdrs (cost=0.00..12.75 rowss width=8) (actual time=0.001..0.001 rows=0 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone) - Seq Scan on cdrs_06 cdrs (cost=0.00..12.99 rows42 width=8) (actual time=5.989...525 rows18 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone)
mozna jakos wymusic uzycie indexu w takim przypadku ?
postgres 8.3.1
Lukasz D.
No ale select na "mamusi" jest przerabiany na UNION ALL - jak sobie wyobrażasz użycie indexu tutaj? A je¶li zależy Ci na tym co jest konkretnie w tabeli "mamusi" to jest zdaje się takie słowo ONLY.
mam ma?y problem z partycjonowaniem.
Zapytanie bezposrednio na tabeli dziecku uzywa indexu
EXPLAIN ANALYZE SELECT id_record from cdrs_06 where start_time '-06-01' order by id_record desc limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.07 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1) - Index Scan Backward using cdrs_06_pk on cdrs_06 (cost=0.00..92.28 rows48 width=8) (actual time=0.027..0.027 rows=1 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone) Total runtime: 0.046 ms (4 rows)
ale juz zapytanie na "mamusi" uzywa na dziecku seqscana
EXPLAIN ANALYZE SELECT id_record from cdrs where start_time '-06-01' order by id_record desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost2.04..12.05 rows=1 width=8) (actual time155.076..5.077 rows=1 loops=1) - Sort (cost2.04..92.45 rows61 width=8) (actual time155.074..5.074 rows=1 loops=1) Sort Key: public.cdrs.id_record Sort Method: top-N heapsort Memory: 25kB - Result (cost=0.00..51.24 rows61 width=8) (actual time=5.992..8.274 rows18 loops=1) - Append (cost=0.00..51.24 rows61 width=8) (actual time=5.991..7.506 rows18 loops=1) - Seq Scan on cdrs (cost=0.00..12.75 rowss width=8) (actual time=0.001..0.001 rows=0 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone) - Seq Scan on cdrs_06 cdrs (cost=0.00..12.99 rows42 width=8) (actual time=5.989...525 rows18 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone)
mozna jakos wymusic uzycie indexu w takim przypadku ?
postgres 8.3.1
Lukasz D. wrote at -06-17 14:22:
włączyłeś constraint_exclusion?
postgresql.org/docs/8.3/static/ddl-partitioning.html 6. Ensure that the constraint_exclusion configuration parameter is enabled in postgresql.conf. Without this, queries will not be optimized as desired.
byc może pomoże - nie wiem na 100% bo jak testowałem to miałem troche inaczej, ale spróbuj. poza tym upewnij się że czegoś nie pominąłeś - tam masz listę co trzeba zrobić.
Lukasz D. wrote at -06-17 14:22: mam ma?y problem z partycjonowaniem.
Zapytanie bezposrednio na tabeli dziecku uzywa indexu
EXPLAIN ANALYZE SELECT id_record from cdrs_06 where start_time '-06-01' order by id_record desc limit 1;
PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.07 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1) - Index Scan Backward using cdrs_06_pk on cdrs_06 (cost=0.00..92.28 rows48 width=8) (actual time=0.027..0.027 rows=1 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone) Total runtime: 0.046 ms (4 rows)
ale juz zapytanie na "mamusi" uzywa na dziecku seqscana
w??czy?e? constraint_exclusion?
postgresql.org/docs/8.3/static/ddl-partitioning.html 6. Ensure that the constraint_exclusion configuration parameter is enabled in postgresql.conf. Without this, queries will not be optimized as desired.
byc mo?e pomo?e - nie wiem na 100% bo jak testowa?em to mia?em troche inaczej, ale spróbuj. poza tym upewnij si? ?e czego? nie pomin??e? - tam masz list? co trzeba zrobi?.
EXPLAIN ANALYZE SELECT id_record from cdrs where start_time '-06-01' order by id_record desc limit 1;
PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost2.04..12.05 rows=1 width=8) (actual time155.076..5.077 rows=1 loops=1) - Sort (cost2.04..92.45 rows61 width=8) (actual time155.074..5.074 rows=1 loops=1) Sort Key: public.cdrs.id_record Sort Method: top-N heapsort Memory: 25kB - Result (cost=0.00..51.24 rows61 width=8) (actual time=5.992..8.274 rows18 loops=1) - Append (cost=0.00..51.24 rows61 width=8) (actual time=5.991..7.506 rows18 loops=1) - Seq Scan on cdrs (cost=0.00..12.75 rowss width=8) (actual time=0.001..0.001 rows=0 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone) - Seq Scan on cdrs_06 cdrs (cost=0.00..12.99 rows42 width=8) (actual time=5.989...525 rows18 loops=1) Filter: (start_time '-06-01 '::timestamp without time zone)
mozna jakos wymusic uzycie indexu w takim przypadku ?
postgres 8.3.1
Filip Rembia?kowski
QUERY
A gdzies napisalem ze wybiera mi wszystkie partycje a nie partycje spelniajace warunek? Co ma piernik do wiatraka? Mnie interesuje dlaczego wybiera seqscana zamiast uzyc indeksu.
QUERY