Re: [SQL] query doesn't always follow 'correct' path..

[Frank Lanitz]

Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and how I can fix it?

Looks a bit like wrong statistics. Are the statistiks for your tables
correct?

Cheers,
Frank


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

[SQL] HP and libxnet ...

[Sebastien FLAESCH]

Hi all,

We have issues on HP with the libpq.so client library when doing networking.

It appears that psql is linked with libxnet.so, but not libpq.so ...

psql can connect to a remove PostgreSQL server, but a simple C program using
the libpq client library cannot, unless we link the program with libxnet.so,
or we use LD_PRELOAD_ONCE=/lib/pa20_64/libxnet.sl ...

In fact we provide a .so library that is linked to libpq.so. The library
can be loaded dynamically by other executables, which do not seem to need
libxnet.so ... so what is the solution?

Should our bins be linked with libxnet on HP, just because of PostgreSQL?

What is the story behind libxnet?

Is there some doc/howto available?

Thanks!
Seb


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

Re: [SQL] query doesn't always follow 'correct' path..

[Bert]

Hello,

yes, the tables are vacuumed every day with the following command: vacuum
analyze schema.table.
The last statistics were collected yesterday evening. I collected
statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are run in the
morning, or in the evening.

I have also run the query with set seq_scan to off, and then I get the
following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast. That's the
one I would always want to use :-)

it's also weird that this is default plan for the biggest partition. But
the smaller the partition gets, the smaller the partition gets.
So I don't think it has anything to do with the memory settings. Since it
already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz  wrote:

> Am 18.02.2013 10:43, schrieb Bert:
> > Does anyone has an idea what triggers this bad plan, and how I can fix
> it?
>
> Looks a bit like wrong statistics. Are the statistiks for your tables
> correct?
>
> Cheers,
> Frank
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
Bert Desmet
0477/305361

Re: [SQL] query doesn't always follow 'correct' path..

[Bert]

Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after the
change of course -> now I only got 2 plans anymore, in stead of 3
cpu_tuple_cost = 0.1 -> by setting this value the seq scans were stopped,
and the better index_only scan / bitmap index scan were used for this
query.

Thank you Robe and Mabe_ for helping me with this issue!

wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert  wrote:

> Hello,
>
> yes, the tables are vacuumed every day with the following command: vacuum
> analyze schema.table.
> The last statistics were collected yesterday evening. I collected
> statistics about the statistics, and I found the following:
> table_name; starttime; runtime
> "st_itemseat";"2013-02-17 23:48:42";"00:01:02"
> "st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
> "st_itemzone";"2013-02-17 23:35:33";"00:00:01"
>
> st_itemseat_45 is a child-partition of st_itemseat.
>
> They seem to be pretty much up to date I guess?
> I also don't get any difference in the query plans when they are run in
> the morning, or in the evening.
>
> I have also run the query with set seq_scan to off, and then I get the
> following output:
> Total query runtime: 12025 ms.
> 20599 rows retrieved.
> and the following plan: http://explain.depesz.com/s/yaJK
>
> These are 3 different plans. And the last one is blazingly fast. That's
> the one I would always want to use :-)
>
> it's also weird that this is default plan for the biggest partition. But
> the smaller the partition gets, the smaller the partition gets.
> So I don't think it has anything to do with the memory settings. Since it
> already chooses this plan for the bigger partitions...
>
> wkr,
> Bert
>
>
> On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz wrote:
>
>> Am 18.02.2013 10:43, schrieb Bert:
>> > Does anyone has an idea what triggers this bad plan, and how I can fix
>> it?
>>
>> Looks a bit like wrong statistics. Are the statistiks for your tables
>> correct?
>>
>> Cheers,
>> Frank
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
>
> --
> Bert Desmet
> 0477/305361
>



-- 
Bert Desmet
0477/305361

Re: [SQL] query doesn't always follow 'correct' path..

[Виктор Егоров]

2013/2/18 Bert 

> When I don't touch the indexscan setting I get the following output:
> Total query runtime: 611484 ms.
> 20359 rows retrieved.
> and the following plan: http://explain.depesz.com/s/sDy
>
> However, when I put set enable_indexscan=off; in fron of the same query I
> get the following output:
> Total query runtime: 16281 ms.
> 20599 rows retrieved.
> and the followign plan: http://explain.depesz.com/s/EpP
>

Is this a typo or do you really get different number of rows returned with
and without indexscans?
Is this expected for the same query to return different sets over time?


-- 
Victor Y. Yegorov

Re: [SQL] query doesn't always follow 'correct' path..

[Julien Cigar]

On 02/18/2013 15:39, Bert wrote:

Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after 
the change of course -> now I only got 2 plans anymore, in stead of 3


default_statistics_target = 5000 as a default is *way* too high. Such 
high values should only be set on a per-column basis ...


cpu_tuple_cost = 0.1 -> by setting this value the seq scans were 
stopped, and the better index_only scan / bitmap index scan were used 
for this query.


Thank you Robe and Mabe_ for helping me with this issue!


s/Mabe_/Mage_ :-)



wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert > wrote:


Hello,

yes, the tables are vacuumed every day with the following command:
vacuum analyze schema.table.
The last statistics were collected yesterday evening. I collected
statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are
run in the morning, or in the evening.

I have also run the query with set seq_scan to off, and then I get
the following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast.
That's the one I would always want to use :-)

it's also weird that this is default plan for the biggest
partition. But the smaller the partition gets, the smaller the
partition gets.
So I don't think it has anything to do with the memory settings.
Since it already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz
mailto:fr...@frank.uvena.de>> wrote:

Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and how
I can fix it?

Looks a bit like wrong statistics. Are the statistiks for your
tables
correct?

Cheers,
Frank


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




-- 
Bert Desmet

0477/305361




--
Bert Desmet
0477/305361



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Relyea, Mike]

> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] 
> On Behalf Of Don Parris
> Sent: Thursday, February 14, 2013 8:58 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Summing & Grouping in a Hierarchical Structure
> 
> Hi all,
> I posted to this list some time ago about working with a hierarchical 
> category structure.   I had great difficulty with my problem and gave up for 
> a time.  
> I recently returned to it and resolved a big part of it.  I have one step 
> left to go, but at least I have solved this part.
> 
> Here is the original thread (or one of them):
> http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com
> 
> 
> Here is my recent blog post about how I managed to show my expenses summed 
> and grouped by a mid-level category:
> http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/
> 
> 
> Specifically, I wanted to sum and group expenses according to categories, not 
> just at the bottom tier, but at higher tiers, so as to show more summarized 
> information.  
> A CEO primarily wants to know the sum total for all the business units, yet 
> have the ability to drill down to more detailed levels if something is 
> unusually high or low.  
> In my case, I could see the details, but not the summary.  Well now I can 
> summarize by what I refer to as the 2nd-level categories.
> Anyway, I hope this helps someone, as I have come to appreciate - and I mean 
> really appreciate - the challenge of working with hierarchical structures in 
> a 2-dimensional RDBMS.  
> If anyone sees something I should explain better or in more depth, please let 
> me know.
> 
> Regards,
> Don
> -- 
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
> http://dcparris.net/
> GPG Key ID: F5E179BE

My two cents would be to actually use a  different tool for the job of 
presenting this data.  I'd have used a pivot table in Microsoft Excel.  Not 
sure what your environment or requirements are but pivot tables are widely used 
in business, easy to share, can be formatted, and give the user the ability to 
drill down and navigate to the data they want to see.
I'd set up a query to pull the raw data you need with all of the categories and 
associated data you need.  Then bring that data to Excel to present and 
summarize it.

Mike


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

Re: [SQL] query doesn't always follow 'correct' path..

[Julien Cigar]

On 02/18/2013 16:20, Julien Cigar wrote:

On 02/18/2013 15:39, Bert wrote:

Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after 
the change of course -> now I only got 2 plans anymore, in stead of 3


default_statistics_target = 5000 as a default is *way* too high. Such 
high values should only be set on a per-column basis ...


oops.. it's per-table and not per-column



cpu_tuple_cost = 0.1 -> by setting this value the seq scans were 
stopped, and the better index_only scan / bitmap index scan were used 
for this query.


Thank you Robe and Mabe_ for helping me with this issue!


s/Mabe_/Mage_ :-)



wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert > wrote:


Hello,

yes, the tables are vacuumed every day with the following
command: vacuum analyze schema.table.
The last statistics were collected yesterday evening. I collected
statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are
run in the morning, or in the evening.

I have also run the query with set seq_scan to off, and then I
get the following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast.
That's the one I would always want to use :-)

it's also weird that this is default plan for the biggest
partition. But the smaller the partition gets, the smaller the
partition gets.
So I don't think it has anything to do with the memory settings.
Since it already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz
mailto:fr...@frank.uvena.de>> wrote:

Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and
how I can fix it?

Looks a bit like wrong statistics. Are the statistiks for
your tables
correct?

Cheers,
Frank


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




-- 
Bert Desmet

0477/305361




--
Bert Desmet
0477/305361



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

Re: [SQL] query doesn't always follow 'correct' path..

[Bert]

Hello,

there were 3 hours in between the 2 queries. so I guess new data was loaded
already. new data is being loaded with that etl_run_id.

wkr,
Bert


On Mon, Feb 18, 2013 at 4:20 PM, Виктор Егоров  wrote:

> 2013/2/18 Bert 
>
>> When I don't touch the indexscan setting I get the following output:
>> Total query runtime: 611484 ms.
>> 20359 rows retrieved.
>> and the following plan: http://explain.depesz.com/s/sDy
>>
>> However, when I put set enable_indexscan=off; in fron of the same query I
>> get the following output:
>> Total query runtime: 16281 ms.
>> 20599 rows retrieved.
>> and the followign plan: http://explain.depesz.com/s/EpP
>>
>
> Is this a typo or do you really get different number of rows returned with
> and without indexscans?
> Is this expected for the same query to return different sets over time?
>
>
> --
> Victor Y. Yegorov
>



-- 
Bert Desmet
0477/305361