Forum PHP.pl > zapytanie na duzej tabeli

Pomoc - Szukaj - Użytkownicy - Kalendarz

Forum PHP.pl > Forum > Bazy danych > PostgreSQL

mac9

10.04.2007, 12:01:51

Witam,

mam dosc spora tabele ktora trzyma staty odwiedzin (kilka milionow rekordow)

chce dostac ilosc unikalnych odwiedzin na stronie nr "3" od poczatku lutego:

[SQL] pobierz, plaintext 
SELECT count(DISTINCT(ip)) FROM staty WHERE date_trunc('month', klikniete_data) >1 AND strona_id = 3 ;
[SQL] pobierz, plaintext

indexy btree sa na klikniete_data oraz na strona_id

niby proste zapytanie ale bardzo dlugo mieli i zamula caly serwer.

explain daje cod takiego:

Kod

Limit  (cost=128878.77..128878.78 rows=1 width=17)
   ->  Aggregate  (cost=128878.77..128878.78 rows=1 width=17)
         ->  Bitmap Heap Scan on staty  (cost=224.93..128846.64 rows=12850 width=17)
               Recheck Cond: (strona_id = 3)
               Filter: ((date_trunc('month'::text, klikniete_data))::text > '3'::text)
               ->  Bitmap Index Scan on strona_id_idx  (cost=0.00..224.93 rows=38551 width=0)
                     Index Cond: (strona_id = 3)

DeyV

15.04.2007, 21:49:34

jaki wynik i explain da zapytanie

[SQL] pobierz, plaintext 
SELECT count( ip ) FROM staty WHERE date_trunc('month', klikniete_data) >1 AND strona_id = 3 ;
[SQL] pobierz, plaintext

mac9

17.04.2007, 12:11:05

witam,

ciagle nie moge znalezc skutecznego rozwiazania dla tego problemu:

stworzylem sobie widok ktory wyciaga staty z kwietnia jedynie:

create view kwiecien_stats as select from kliki gdzie data z kwietnia..

tyle tylko ze nie wiem po co ten widok jest skoro zapytanie zapuszczone na tym wiidoku i tak mieli 40000 rekordow(tak mowi explain), czyli zachowuje sie tak jak byloby zapuszczone na tabeli glownej "staty".. a widok ma tylko 1500 wierszy..

nie rozumiem tego..

wojtekmki

18.04.2007, 14:06:22

witam

hmm... a czy przypadkiem date_trunc nie daje ci innego wyniku

Cytat

Examples:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

więc wg mnie zapytanie powinno wyglądać raczej tak (przy typie TIMESTAMP dla pola klikniete_data):

Cytat("SQL")

select count(distinct(ip)) from staty where date_trunc('month', klikniete_data) >= '2007-02-01 00:00:00' and strona_id = 3 ;

Ewentualnie przy takiej ilości danych sugeruję utworzyć dodatkową tabelę w której będą odpowiednio już przetworzone (statystyki miesięczne, roczne itp). Czasochłonną operację wykonasz raz (np. za m-c luty 2007 - przy takiej ilości danych to rozwiązanie będzie efektywniejsze), i później możesz z wyników korzystać do woli

Oczywiście strukturę tabel wynikowych musisz przemyśleć w zależności od tego jakiego rodzaju wyniki Cię interesują

pozdrawiam

DeyV

18.04.2007, 20:31:12

Odpowiedz na moje pytanie....

wojtekmki

19.04.2007, 06:46:27

Cytat(DeyV @ 18.04.2007, 21:31:12 )

Odpowiedz na moje pytanie....

cześć

mianowicie

maryaan

19.04.2007, 07:03:17

DeyV zadal tylko jedno pytanie wiec za duzego wyboru nie masz...

Cytat(DeyV @ 15.04.2007, 22:49:34 )

jaki wynik i explain da zapytanie

[SQL] pobierz, plaintext 
SELECT count( ip ) FROM staty WHERE date_trunc('month', klikniete_data) >1 AND strona_id = 3 ;
[SQL] pobierz, plaintext

wojtekmki

19.04.2007, 07:46:05

Cytat(maryaan @ 19.04.2007, 08:03:17 )

DeyV zadal tylko jedno pytanie wiec za duzego wyboru nie masz...

hmm...

Kod

select count( ip ) from staty where date_trunc('month', klikniete_data) > 1 and strona_id = 3;

wg mnie zliczy wszystkie ip "jak leci" dla strona_id = 3

Kod

select count(distinct(ip)) from staty where date_trunc('month', klikniete_data) > 1 and strona_id = 3;

to zliczy ilość unikalnych dla strona_id = 3

ale może się mylę...

Jednak przy dużych ilościach danych osobiście wolę "generować" tego typu wyniki raz i zapisywać je w tabeli - często w przyszłości się przydają.

pozdrawiam

PS. a jakie masz założone indeksy

data + strona_id

jeśli tak to może pomyśl nad indeksem funkcyjnym...

Kod

CREATE INDEX nazwa ON tabela USING btree (date_trunc('month', klikniete_data),strona_id);

piszę "z palca" więc sprawdź czy zadziała

DeyV

19.04.2007, 13:04:05

ehh - chodzi właśnie o explain tego mojego zapytania.
Dlaczego? Bo chciałem sprawdzić, czy czasem za problemy z wydajnością nie jest odpowiedzialny brak indexu na IP.

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.