Re: [PERFORM] Hints (was Poor performance using CTE)

2012-11-22 Thread Heikki Linnakangas 

On 22.11.2012 02:53, Jeff Janes wrote:

That gives the planner the information it needs to choose the right plan on
its own. That kind of hints would be much less implementation specific and
much more likely to still be useful, or at least not outright
counter-productive, in a future version with a smarter planner.


When I run into unexpectedly poor performance, I have an intuitive
enough feel for my own data that I know what plan it ought to be
using.  Figuring out why it is not using it is very hard.  For one
thing, EXPLAIN tells you about the "winning" plan, but there is no
visibility into what ought to be the winning plan but isn't, so no way
to see why it isn't.So you first have to use our existing non-hint
hints (enable_*, doing weird things with cost_*, CTE stuff) to trick
it into using the plan I want it to use, before I can figure out why
it isn't using it, before I could figure out what hints of the style
you are suggesting to supply to get it to use it.


I'm sure that happens too, but my gut feeling is that more often the 
EXPLAIN ANALYZE output reveals a bad estimate somewhere in the plan, and 
the planner chooses a bad plan based on the bad estimate. If you hint 
the planner by giving a better estimate for where the estimator got it 
wrong, the planner will choose the desired plan.


- Heikki


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] fast read of binary data

2012-11-22 Thread Heikki Linnakangas 

On 22.11.2012 09:54, Eildert Groeneveld wrote:

ok, I did have a look at the libpq librar, and you are right, there is a
way to obtain binary data from the backend through the  PQexecParams

 res = PQexecParams(conn,
"DECLARE myportal CURSOR FOR select genotype_bits
from v_genotype_data",
0,   /* zero param */
NULL,/* let the backend deduce param type */
paramValues,
NULL,/* don't need param lengths since text*/
NULL,/* default to all text params */
1);  /* ask for binary results */

genotype_bits is defined as bit varying in the backend. When writing the
results:
 for (i = 0; i<  PQntuples(res); i++)
 {
 for (j = 0; j<  nFields; j++)
   fwrite(PQgetvalue(res, i, j),10,1,f);
 }

it is clear that the results are NOT in binary format:
eg(eno,snp): od -b junk |head
000 061 060 061 060 061 060 061 060 061 060 061 060 061 060 061 060


You need to ask for binary results when you execute the FETCH 
statements. Asking for binary results on the DECLARE CURSOR statement 
has no effect, as DECLARE CURSOR doesn't return any results; it's the 
FETCH that follows that returns the result set.


- Heikki


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] PQconnectStart/PQconnectPoll

2012-11-22 Thread Craig Ringer 
On 11/15/2012 05:02 PM, Sergio Mayoral wrote:
>
> PQconnectdb is taking too long comparing to mysql and i found out the
> time is consumed by PQconnectPoll waiting for the socket to be ready
> for reading/writing
What's "too long"?
>
> I cannot use persistent connections. I must open/close a connection
> anytime I want to insert something new.
If you mean that you intend to open a new connection, do a single
INSERT, and close the connection - your performance will be awful.

If your app can't use persistent or pooled connections, use PgBouncer as
an external connection pool. Have your app connect to PgBouncer, and
PgBouncer connect to PostgreSQL.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[PERFORM] SQL performance question

2012-11-22 Thread Niklas Paulsson 

Hello,

This is a performance question that has held me occupied for quite some 
time now,


The following join is a somewhat slow query:

(np_artikel, sm_artikel_dim are views and sm_orderrad_* are tables )


xtest=# explain analyze verbose
select * from np_artikel np
join sm_artikel_dim dim on np.artikelid = dim.artikelid
join sm_orderrad ord on ord.artikelid = np.artikelid
JOIN sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr
WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912;

See: http://explain.depesz.com/s/stI

 Total runtime: 47748.786 ms
(140 rows)



This is somewhat strange - beacause i look for i single order-row in a 
specific order-batch which only returns one article-id. Please see the 
following three questions.





xtest=# SELECT distinct artikelid FROM sm_orderrad ORD JOIN 
sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR 
= 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912;

 artikelid
---
 301206
(1 row)

xtest=# explain analyze verbose SELECT distinct artikelid FROM 
sm_orderrad ORD JOIN sm_orderrad_storlek STL ON ORD.ordradnr = 
STL.ordradnr WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR = 3 AND 
ORD.BATCHNR=3616912;


See: http://explain.depesz.com/s/kI2

 Total runtime: 0.256 ms
(13 rows)

xtest=# explain analyze verbose select * from np_artikel np join 
sm_artikel_dim dim on np.artikelid = dim.artikelid where np.artikelid 
=301206;


See: http://explain.depesz.com/s/fFN

 Total runtime: 2.563 ms
(99 rows)




Getting the same result from a question where I use a fixed article-id 
is about 23 000 times faster .


Perhaps if use a subquery?




xtest=# explain analyze select * from np_artikel np join sm_artikel_dim 
dim on np.artikelid = dim.artikelid where np.artikelid in ( SELECT 
distinct artikelid FROM sm_orderrad ORD JOIN sm_orderrad_storlek STL ON 
ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR 
= 3 AND ORD.BATCHNR=3616912);


See:http://explain.depesz.com/s/wcD )

 Total runtime: 45542.462 ms
(90 rows)



No, not much luck there either ..

CTE's are cool, or so I've heard atleast ...



xtest=# explain analyze verbose
WITH orders AS  ( SELECT distinct artikelid FROM sm_orderrad ORD JOIN 
sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR 
= 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912) 


 select * from np_artikel np
 join sm_artikel_dim dim on np.artikelid = dim.artikelid
 join orders on np.artikelid=orders.artikelid;

See: http://explain.depesz.com/s/1a2

 Total runtime: 44966.271 ms
(145 rows)



But they aren't much faster than a join, obviously.

My question is the following: Would it be possible to rewrite the query 
in such a way or use some kind of server-setting/tuning so it will get 
as fast as when I query with a single article-id as argument?



--
+46 734 307 163 (mobile)
www.lodon.se

Besöksadress:
Lodon AB
Vingalandsgatan 8
417 63 Göteborg


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Poor performance using CTE

2012-11-22 Thread Jeremy Harris 

On 22/11/2012 00:08, Craig Ringer wrote:

WITH
   FENCE foo AS (SELECT ...),
   bar AS (SELECT ...)
SELECT * FROM bar;

Are we fencing just foo? Or all expressions?



WITH foo AS (FENCED SELECT ...),
 bar AS (SELECT ...),
SELECT ... ;

--
Jeremy




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Poor performance using CTE

2012-11-22 Thread Jon Nelson 
On Thu, Nov 22, 2012 at 7:42 AM, Jeremy Harris  wrote:
> On 22/11/2012 00:08, Craig Ringer wrote:
>>
>> WITH
>>FENCE foo AS (SELECT ...),
>>bar AS (SELECT ...)
>> SELECT * FROM bar;
>>
>> Are we fencing just foo? Or all expressions?
>>
>
> WITH foo AS (FENCED SELECT ...),
>  bar AS (SELECT ...),
> SELECT ... ;

I would much rather see 'MATERIALIZE' instead of 'FENCED', unless the
by the latter you mean to forbid *all* optimizations, whereas with the
latter the meaning is pretty clear.

-- 
Jon


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: SOLVED - RE: [PERFORM] Poor performance using CTE

2012-11-22 Thread Vitalii Tymchyshyn 
I'd also add ANALYZED/NOT ANALYZED. This should force it behave like
'create table, analyze, select' with statistics used in second query plan.

P.S. defaults can be configurable.
20 лист. 2012 02:22, "Gavin Flower"  напис.

> On 15/11/12 15:03, Peter Geoghegan wrote:
>
>> On 15 November 2012 01:46, Andrew Dunstan  wrote:
>>
>>> It cuts both ways. I have used CTEs a LOT precisely because this
>>> behaviour
>>> lets me get better plans. Without that I'll be back to using the "offset
>>> 0"
>>> hack.
>>>
>> Is the "OFFSET 0" hack really so bad? We've been telling people to do
>> that for years, so it's already something that we've effectively
>> committed to.
>>
>>  How about adding the keywords FENCED and NOT FENCED to the SQL
> definition of CTE's - with FENCED being the default?
>
>
> Cheers,
> Gavin
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.**
> org )
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance
>