zapytanie na duzej tabeli, select count(distinct(ip)) ... Opcje

[mac9]

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]

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]

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]

witam

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

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):

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

Ten post edytował wojtekmki 18.04.2007, 14:16:24

[DeyV]

Odpowiedz na moje pytanie....

[wojtekmki]

Odpowiedz na moje pytanie....

cześć

mianowicie

[maryaan]

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

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]

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

Ten post edytował wojtekmki 19.04.2007, 11:22:00

[DeyV]

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.