Re: [PERFORM] Optimizer is not choosing index

2012-02-15 Thread Markus Innerebner
Hi Tom, thanks for your suggestion: > Markus Innerebner writes: >> The query plan says, that a sequential scan is performed on the edge table. >> I consider it strange that he is not accessing on the (btree) index one the >> edge table. > > This suggests that you have a datatype mismatch: >

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Han Zhou
Hi Andres, Good hint! DBRNWHSB=# COPY (SELECT * FROM my_large) TO '/tmp/somefile'; COPY 73728 Time: 1405.976 ms DBRNWHSB=# COPY (SELECT * FROM my_large) TO '/tmp/somefile_binary' BINARY ; COPY 73728 Time: 840.987 ms DBRNWHSB=# EXPLAIN ANALYZE SELECT * FROM my_large;

Re: [PERFORM] Optimizer is not choosing index

2012-02-15 Thread Tom Lane
Markus Innerebner writes: > The query plan says, that a sequential scan is performed on the edge table. I > consider it strange that he is not accessing on the (btree) index one the > edge table. This suggests that you have a datatype mismatch: > " Hash Cond: ((e.target)::numeric = n.id)" Yo

[PERFORM] Optimizer is not choosing index

2012-02-15 Thread Markus Innerebner
hi all In my query I have two tables (edges 3,600,000 tuples and nodes 1,373,00 tuples), where I want to obtain all edges,whose target vertex is within a given euclidean range starting from a query point q: the query is formulated as following: SELECT E.ID, E.SOURCE,E.SOURCE_MODE,E.TARGET,E

Re: [PERFORM] UPDATE on NOT JOIN

2012-02-15 Thread Marti Raudsepp
On Wed, Feb 15, 2012 at 20:33, Gabriel Biberian wrote: > Currently, i use the following query to update the filesystem table with the > missing files : > UPDATE filesystem SET dead=some_value WHERE dead=0 AND (SELECT 1 FROM > temporary AS t WHERE t.hash=filesystem.hash LIMIT 1) IS NULL I don't kn

[PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Zhou Han
Hi, Forward my question here. Best regards, Han -- Forwarded message -- From: Zhou Han Date: Wed, Feb 15, 2012 at 3:01 PM Subject: Re: [HACKERS] client performance v.s. server statistics To: Amit Kapila Cc: pgsql-hack...@postgresql.org Hi, I have tried unix domain socket and

[PERFORM] UPDATE on NOT JOIN

2012-02-15 Thread Gabriel Biberian
Hello, I'm working on a system that detects changes in someone's filesystem. We first scan the entire filesystem which is dumped into the table 'filesystem' containing the full_path of every file and it's corresponding md5 hash. We then do subsequent scans of the filesystem which are dumped int

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Andres Freund
On Wednesday, February 15, 2012 12:33:13 PM Han Zhou wrote: > Hi, > > To be more specific, I list my calculation here: > The timing shown in psql may include: plan + execution + copying to > result set in backend (does this step exist?) + transferring data to > client via socket. Correct. > Then

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Han Zhou
Hi, To be more specific, I list my calculation here: The timing shown in psql may include: plan + execution + copying to result set in backend (does this step exist?) + transferring data to client via socket. Then I want to know what's the time shown in pg_stat_statement and EXPLAIN ANALYZE in te

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Han Zhou
Hi Andres, May you missed my first post, and I paste it here again: In our environment sequential scanning (select * from ...) for a table with tens of thousands of record costs 1 - 2 seconds, regardless of using ODBC driver or the "timing" result shown in psql client (which in turn, relies on lib

Re: [PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Andres Freund
Hi, On Wednesday, February 15, 2012 11:19:00 AM Zhou Han wrote: > I have tried unix domain socket and the performance is similar with > TCP socket. It is MIPS architecture so memory copy to/from kernel can > occupy much time, and apparently using unit domain socket has no > difference than TCP in t

[PERFORM] Fwd: [HACKERS] client performance v.s. server statistics

2012-02-15 Thread Zhou Han
Hi, Forward my question from HACKERS list to here (and added some more notes): I have tried unix domain socket and the performance is similar with TCP socket. It is MIPS architecture so memory copy to/from kernel can occupy much time, and apparently using unit domain socket has no difference than