Re: [PERFORM] pg_database_size
On Sat, Jun 4, 2016 at 5:35 PM, sangeetha wrote:
> SELECT pg_database_size('DB1') takes 60ms for 7948 kB size DB. Is there
> any
> way to reduce the time taken or any other ways to find data base size?
What is the version of PostgreSQL you are using ?
You can execute the command "\l+" which will list all the databases and
their sizes.
Or you can execute "\l+ ".
Regards,
Venkata B N
Re: [PERFORM] Primary key index suddenly became very slow
On Mon, Feb 8, 2016 at 9:04 PM, Gustav Karlsson wrote: > Additional information: > > The problematic row has likely received many hot updates (100k+). Could > this be a likely explanation for the high execution time? > Query immediately after the bulk updates before VACUUM will take longer time. Since the VACUUM might have cleared the dead tuples and might have updated the hint-bits, the query's execution time has become much better. As the updates are hot, you may not need to consider other factors like, table size growth and if the indexes have grown in size. Regards, Venkata B N Fujitsu Australia > > > On Feb 8, 2016, at 10:45 AM, Gustav Karlsson > wrote: > > Hi, > > Question: > > What may cause a primary key index to suddenly become very slow? Index > scan for single row taking 2-3 seconds. A manual vacuum resolved the > problem. > > > Background: > > We have a simple table ‘KONTO’ with about 600k rows. > > > Column|Type | Modifiers > > --+-+--- > id | bigint | not null > ... > > Indexes: > "konto_pk" PRIMARY KEY, btree (id) > ... > > > Over the weekend we experienced that lookups using the primary key index > (‘konto_pk’) became very slow, in the region 2-3s for fetching a single > record: > > QUERY PLAN > Index Scan using konto_pk on konto (cost=0.42..6.44 rows=1 width=164) > (actual time=0.052..2094.549 rows=1 loops=1) > Index Cond: (id = 2121172829) > Planning time: 0.376 ms > Execution time: 2094.585 ms > > > After a manual Vacuum the execution time is OK: > > QUERY PLAN > Index Scan using konto_pk on konto (cost=0.42..6.44 rows=1 width=164) > (actual time=0.037..2.876 rows=1 loops=1) > Index Cond: (id = 2121172829) > Planning time: 0.793 ms > Execution time: 2.971 ms > > > So things are working OK again, but we would like to know what may cause > such a degradation of the index scan, to avoid this happening again? (We > are using Postgresql version 9.4.4) > > > > Regards, > Gustav > > >
Re: [PERFORM] Slow Query
On Wed, Aug 12, 2015 at 3:29 PM, robbyc wrote:
> Hi Venkata,
>
> work_mem was set to 72MB, increased to 144MB, no change.
>
Increasing work_mem depends on various other factors like Table size
(amount of data being sorted), available memory etc.
> Added an index of type varchar_pattern_ops to Vacancy.JobTitle, this did
> not help either.
>
Sorry, I did not mean to say that an Index must be added straight away. The
column must be eligible to have an Index. Meaning, Index will be
beneficial if created on a column with high number of distinct values.
If either of the above does not help, then options to rewrite the query
must be explored.
Thanks,
Venkata Balaji N
Fujitsu Australia
>
> On Wed, Aug 12, 2015 at 2:09 PM, Venkata Balaji N [via PostgreSQL] <[hidden
> email] <http:///user/SendEmail.jtp?type=node&node=5861850&i=0>> wrote:
>
>> On Wed, Aug 12, 2015 at 12:34 PM, robbyc <[hidden email]
>> <http:///user/SendEmail.jtp?type=node&node=5861839&i=0>> wrote:
>>
>>> Hi,
>>>
>>> I am new to optimizing queries and i'm getting a slow running time
>>> (~1.5secs) with the following SQL:
>>>
>>> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle",
>>> "Vacancy"."DateCreated", "Vacancy"."CustomAccess"
>>> , "Department"."Name" as "Department", list("Occupation"."Name") as
>>> "Occupation", "Vacancy"."PositionNo"
>>> , "Vacancy"."Template" from
>>>"Vacancy"
>>> LEFT JOIN "CategoryOption_TableRow" as "c_22"
>>> ON ("c_22"."RowID" = "Vacancy"."ID"
>>> and "c_22"."Category_TableID" = 22)
>>> LEFT JOIN "CategoryOption" as "Occupation"
>>> ON ("Occupation"."ID" = "c_22"."CategoryOptionID")
>>> LEFT JOIN "TableRow_TableRow" as "t_33"
>>> ON ("t_33"."Table1RowID" = "Vacancy"."ID"
>>> and "t_33"."Table_TableID" = 33 )
>>> LEFT JOIN "Department"
>>> ON ("Department"."ID" = "t_33"."Table2RowID" and
>>> "Department"."Active" = 't' and "Department"
>>> ."ClientID" = 263)
>>> JOIN "CategoryOption_TableRow" as "c_50" ON ("c_50"."Category_TableID"=
>>> 50
>>> and "c_50"."RowID" = "Vacancy"
>>> ."ID" and "c_50"."CategoryOptionID"=19205)
>>> WHERE "Vacancy"."ClientID" = 263 AND "Vacancy"."ID" NOT IN(SELECT
>>> DISTINCT("Vacancy"."ID")
>>> FROM "Vacancy" join "CategoryOption_TableRow" "ct126" on
>>> ("ct126"."Category_TableID" = 126
>>> and "RowID" = "Vacancy"."ID")
>>> left join "Workflow" on ("Workflow"."VacancyID" =
>>> "Vacancy"."ID"
>>> and "Workflow"."Level"
>>> = 1)
>>> left join "CategoryOption_TableRow" "c30" on ("c30"."RowID" =
>>> "Workflow"."ID" and "c30"."Category_TableID"
>>> = 30 and "c30"."CategoryOptionID" = 21923)
>>> WHERE "Template" AND "ct126"."CategoryOptionID"
>>> IN(34024,35254,35255,35256)) and "Vacancy"
>>> ."Template" = 't'
>>> GROUP BY "Vacancy"."ID", "Vacancy"."JobTitle", "Vacancy"."DateCreated",
>>> "Vacancy"."CustomAccess", "Department"
>>> ."Name", "Vacancy"."PositionNo", "Vacancy"."Template"
>>> UNION
>>> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle",
>>> "Vacancy"."DateCreated", "Vacancy"."CustomAccess"
>>> , "Department"."Name" as "Department", list("Occupation"."Name") as
>>> "Occupation", &quo
Re: [PERFORM] Slow Query
On Wed, Aug 12, 2015 at 12:34 PM, robbyc wrote:
> Hi,
>
> I am new to optimizing queries and i'm getting a slow running time
> (~1.5secs) with the following SQL:
>
> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle",
> "Vacancy"."DateCreated", "Vacancy"."CustomAccess"
> , "Department"."Name" as "Department", list("Occupation"."Name") as
> "Occupation", "Vacancy"."PositionNo"
> , "Vacancy"."Template" from
>"Vacancy"
> LEFT JOIN "CategoryOption_TableRow" as "c_22"
> ON ("c_22"."RowID" = "Vacancy"."ID"
> and "c_22"."Category_TableID" = 22)
> LEFT JOIN "CategoryOption" as "Occupation"
> ON ("Occupation"."ID" = "c_22"."CategoryOptionID")
> LEFT JOIN "TableRow_TableRow" as "t_33"
> ON ("t_33"."Table1RowID" = "Vacancy"."ID"
> and "t_33"."Table_TableID" = 33 )
> LEFT JOIN "Department"
> ON ("Department"."ID" = "t_33"."Table2RowID" and
> "Department"."Active" = 't' and "Department"
> ."ClientID" = 263)
> JOIN "CategoryOption_TableRow" as "c_50" ON ("c_50"."Category_TableID"= 50
> and "c_50"."RowID" = "Vacancy"
> ."ID" and "c_50"."CategoryOptionID"=19205)
> WHERE "Vacancy"."ClientID" = 263 AND "Vacancy"."ID" NOT IN(SELECT
> DISTINCT("Vacancy"."ID")
> FROM "Vacancy" join "CategoryOption_TableRow" "ct126" on
> ("ct126"."Category_TableID" = 126
> and "RowID" = "Vacancy"."ID")
> left join "Workflow" on ("Workflow"."VacancyID" =
> "Vacancy"."ID"
> and "Workflow"."Level"
> = 1)
> left join "CategoryOption_TableRow" "c30" on ("c30"."RowID" =
> "Workflow"."ID" and "c30"."Category_TableID"
> = 30 and "c30"."CategoryOptionID" = 21923)
> WHERE "Template" AND "ct126"."CategoryOptionID"
> IN(34024,35254,35255,35256)) and "Vacancy"
> ."Template" = 't'
> GROUP BY "Vacancy"."ID", "Vacancy"."JobTitle", "Vacancy"."DateCreated",
> "Vacancy"."CustomAccess", "Department"
> ."Name", "Vacancy"."PositionNo", "Vacancy"."Template"
> UNION
> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle",
> "Vacancy"."DateCreated", "Vacancy"."CustomAccess"
> , "Department"."Name" as "Department", list("Occupation"."Name") as
> "Occupation", "Vacancy"."PositionNo"
> , "Vacancy"."Template" from
>"Vacancy"
> LEFT JOIN "CategoryOption_TableRow" as "c_22"
> ON ("c_22"."RowID" = "Vacancy"."ID"
> and "c_22"."Category_TableID" = 22)
> LEFT JOIN "CategoryOption" as "Occupation"
> ON ("Occupation"."ID" = "c_22"."CategoryOptionID")
> LEFT JOIN "TableRow_TableRow" as "t_33"
> ON ("t_33"."Table1RowID" = "Vacancy"."ID"
> and "t_33"."Table_TableID" = 33 )
> LEFT JOIN "Department"
> ON ("Department"."ID" = "t_33"."Table2RowID" and
> "Department"."Active" = 't' and "Department"
> ."ClientID" = 263)
> JOIN "CategoryOption_TableRow" as "c_50" ON ("c_50"."Category_TableID"= 50
> and "c_50"."RowID" = "Vacancy"
> ."ID" and "c_50"."CategoryOptionID"=19205)
> WHERE "Vacancy"."ClientID" = 263 AND "Vacancy"."ID" NOT IN(SELECT
> DISTINCT("Vacancy"."ID")
> FROM "Vacancy" join "CategoryOption_TableRow" "ct126" on
> ("ct126"."Category_TableID" = 126
> and "RowID" = "Vacancy"."ID")
> left join "Workflow" on ("Workflow"."VacancyID" =
> "Vacancy"."ID"
> and "Workflow"."Level"
> = 1)
> left join "CategoryOption_TableRow" "c30" on ("c30"."RowID" =
> "Workflow"."ID" and "c30"."Category_TableID"
> = 30 and "c30"."CategoryOptionID" = 21923)
> WHERE "Template" AND "ct126"."CategoryOptionID"
> IN(34024,35254,35255,35256)) and "Vacancy"
> ."Template" <> 't' AND "Vacancy"."Level" = 1
> GROUP BY "Vacancy"."ID", "Vacancy"."JobTitle", "Vacancy"."DateCreated",
> "Vacancy"."CustomAccess", "Department"
> ."Name", "Vacancy"."PositionNo", "Vacancy"."Template"
> ORDER BY "JobTitle"
>
> Running explain analyze gives me the following information:
> http://explain.depesz.com/s/pdC
> For a total runtime: 2877.157 ms
>
> If i remove the left joins on Department and TableRow_TableRow this reduces
> the run time by about a third.
> Additionally removing CategoryOption and CategoryOption_TableRow joins
> further reduces by a about a third.
>
> Given that i need both these joins for the information retrieved by them,
> what would be the best way to re-factor this query so it runs faster?
>
> Looking at the output of explain analyze the hash aggregates and sort seem
> to be the primary issue.
>
The query has got a distinct and group-by/order-by clauses which seems to
be taking time. Without looking at much details of the query code and Table
size etc, did you try increasing the work_mem and then execute the query
and see if that helps ? This will reduce the on-disk IO for sorting. Also,
Vacancy.JobTitle seems to be a non-index column.
Regards,
Venkata Balaji
Fujitsu Australia
