How to speed up the query in PostgreSQL

I have DB in PostgreSQL with a big data (now it is somewhere around 46 GB and the db will keep growing). I created indexes on often used columns and adapted the config file:

shared_buffers = 1GB
temp_buffers = 256MB
work_mem = 512MB

But this query is still slow:

select distinct us_category_id as cat, count(h_user_id) as res from web_hits 
inner join users on h_user_id = us_id 
where (h_datetime)::date = ('2015-06-26')::date and us_category_id != ''
group by us_category_id

Explain Analyze:

HashAggregate (cost=2870958.72..2870958.93 rows=21 width=9) (actual time=899141.683..899141.683 rows=0 loops=1)

Group Key: users.us_category_id, count(web_hits.h_user_id)
-> HashAggregate (cost=2870958.41..2870958.62 rows=21 width=9) (actual time=899141.681..899141.681 rows=0 loops=1)

Group Key: users.us_category_id
 -> Hash Join (cost=5974.98..2869632.11 rows=265259 width=9) (actual time=899141.679..899141.679 rows=0 loops=1)

Hash Cond: ((web_hits.h_user_id)::text = (users.us_id)::text)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
Filter: ((h_datetime)::date = '2015-06-26'::date)

Rows Removed by Filter: 55051918
-> Hash (cost=4292.99..4292.99 rows=134559 width=10) (never executed)
-> Seq Scan on users (cost=0.00..4292.99 rows=134559 width=10) (never executed)
Filter: ((us_category_id)::text <> ''::text)

"Planning time: 1.309 ms"
"Execution time: 899141.789 ms"

Date is changed. How can I speed up the query?

Table and index creating

CREATE TABLE web_hits (
  h_id integer NOT NULL DEFAULT nextval('w_h_seq'::regclass),
  h_user_id character varying,
  h_datetime timestamp without time zone,
  h_db_id character varying,
  h_voc_prefix character varying,
  ...
  h_bot_chek integer, -- 1-бот...
  CONSTRAINT w_h_pk PRIMARY KEY (h_id)
);
ALTER TABLE web_hits OWNER TO postgres;
COMMENT ON COLUMN web_hits.h_bot_chek IS '1-бот, 0-не бот';

CREATE INDEX h_datetime ON web_hits (h_datetime);
CREATE INDEX h_db_index ON web_hits (h_db_id COLLATE pg_catalog."default");
CREATE INDEX h_pref_index ON web_hits (h_voc_prefix COLLATE pg_catalog."default" text_pattern_ops);
CREATE INDEX h_user_index ON web_hits (h_user_id text_pattern_ops);

 CREATE TABLE users (
  us_id character varying NOT NULL,
  us_category_id character varying,
  ...
  CONSTRAINT user_pk PRIMARY KEY (us_id),
  CONSTRAINT cities_users_fk FOREIGN KEY (us_city_home)
      REFERENCES cities (city_id),
  CONSTRAINT countries_users_fk FOREIGN KEY (us_country_home)
      REFERENCES countries (country_id),
  CONSTRAINT organizations_users_fk FOREIGN KEY (us_institution_id)
      REFERENCES organizations (org_id),
  CONSTRAINT specialities_users_fk FOREIGN KEY (us_speciality_id)
      REFERENCES specialities (speciality_id),
  CONSTRAINT us_affiliation FOREIGN KEY (us_org_id)
      REFERENCES organizations (org_id),
  CONSTRAINT us_category FOREIGN KEY (us_category_id)
      REFERENCES categories (cat_id),
  CONSTRAINT us_reading_room FOREIGN KEY (us_reading_room_id)
      REFERENCES reading_rooms (rr_id)
);
ALTER TABLE users OWNER TO sveta;
COMMENT ON COLUMN users.us_type IS '0-аноним, 1-читатель, 2-удаленный';

CREATE INDEX us_cat_index ON users (us_category_id);
CREATE INDEX us_user_index ON users (us_id text_pattern_ops);

No response from server : faulty php script?

So I'm continuing to work on this login.php script

<?php

$link = new MySQLi (localhost, Maelhann, userpassword, Logindata2256 ) ;

if ( mysqli_connect_error() ){

$logmessage = 'MySQL error : ' . mysqli_connect_error() ; 
die('could not connect to database');
}

$Email = $_POST['Email'];
$Password = $_POST['Password'];

 if ( int mysqli_result::$num_rows() == 1 ){

$_SESSION["Email"];
$_SESSION["Password"];
header("location:Newaccount.html");
 }
else{
echo "wrong username or password" ;

}

?>

but all I get when trying to log in is a blank page... any Idea where I could have failed?

How can I make a pseudo-dynamic Stored procedure / CTE

I need to write a sql query where I have a group of students who are in a specific program and display their completion status for each course in the program. The problem is that the program is a parameter to go into the query, and each time I run it (with a different program) each program has a different set of courses associated with it.

So, from a table that relates Students to programs. (StuPRograms) Many to many relationship between Programs and Courses, and then I get a result from another table (Enrollments) that joins Students to Courses. The rows are students while the columns are pivots using the courses, and the cells are the result of if that student has completed the course.

I could do a normal SQL Select with piles of calculated columns. (the columns being the student, the name of the first course, the status of the student in the first course, name of 2nd course, status in 2nd course.. etc..) but that would be crazy slow.

I've used TSQL to generate the data with a dynamic pivot, but because it has dynamic columns I can't put it in a Stored Procedure. (Because the SP needs to generate a constant schema.) So, I'm thinking if I limit my results to only look at a 10 course columns that would give it a static schema, so I can put it in a stored proc.. Effectively I'm looking for a better performing solution then to do 20 column sql statements for each StuProgram row returned.

MySQL Order by + Group by drastically slow down the query

I have the following query that, despite its complexity, performs well in the following shape:

SELECT 

`listings`.*, 
`auth_agents`.*, 
`symbols`.`code` AS `symbol_code`,
`symbols`.`text` AS `symbol_text`,
`symbols`.`position` AS `symbol_position`,
GROUP_CONCAT(DISTINCT IFNULL(`categories_countries`.`translation`, `c`.`name`) ORDER BY CASE WHEN `c`.`category_id` = 106 THEN 1 ELSE `c`.`category_id` END) as `category_names`,
`pv_1`.`value` AS `transaction_type_name`,
`pv_2`.`value` AS `property_type_name`,
`listings_statuses`.`name` AS `status`,
CONCAT(`pv_3`.`value`, 'property/', `listings`.`slug`, '/', `listings`.`listing_id`) AS `url`,
price_range(`listings`.`price_sales_min`, `listings`.`price_sales`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_sales_range`,
price_range(`listings`.`price_lettings_min`, `listings`.`price_lettings`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_lettings_range`,
summary(`listings`.`description`, `listings`.`summary`) AS `summary`,
short_title(`categories`.`name`, `categories`.`title`, `pv_1`.`value`, `listings`.`city`, `listings`.`region`, `listings`.`area`) AS `short_title`,
IF(`listings`.`listings_price_qualifier_id` = 2, TRUE, FALSE) AS `POA`,
IF((SELECT `listings_categories`.`listing_id` FROM `listings_categories` WHERE `listings_categories`.`listing_id` = `listings`.`listing_id` AND `listings_categories`.`category_id` = 124 LIMIT 1) IS NOT NULL, TRUE, FALSE) AS `serviced_office`,

GROUP_CONCAT(DISTINCT CONCAT_WS('|', `auth_agents_staff`.`auth_agents_staff_id`, `auth_staff`.`f_name`, `auth_staff`.`l_name`, `auth_staff`.`photo`)) AS `agents_staff`,
(SELECT CONCAT('http://ift.tt/1GIT2RV', `listings`.`auth_agent_id`, '/', `listings_media`.`filename`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` ORDER BY `default` DESC LIMIT 1) AS `image`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 1) AS `image_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 3) AS `pdf_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 4) AS `floor_plan_count`,


distance(`listings`.`lat`, `listings`.`lng`, 51.5073500, -0.1277600) AS `distance` 

FROM `listings` 
JOIN `listings_coords` ON `listings_coords`.`listing_id` = `listings`.`listing_id` 

JOIN `auth_agents` ON `listings`.`auth_agent_id` = `auth_agents`.`auth_agent_id` 
JOIN `branches` USING (`branch_id`) 
LEFT JOIN `listings_auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `listings_auth_agents_staff`.`auth_agents_staff_id` = `auth_agents_staff`.`auth_agents_staff_id` 
LEFT JOIN `auth` AS `auth_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `auth_agents_staff`.`auth_agents_staff_id` IS NOT NULL AND 
`auth_staff`.`auth_id` = `auth_agents_staff`.`auth_id` 

JOIN `parameters_values` AS `pv_1` 
ON `pv_1`.`parameter_id` = 2 AND `pv_1`.`country_id` = 223 AND `pv_1`.`key` = `listings`.`transaction_type` 

JOIN `parameters_values` AS `pv_2` 
ON `pv_2`.`parameter_id` = 1 AND `pv_2`.`country_id` = 223 AND `pv_2`.`key` = `listings`.`property_type` 

JOIN `parameters_values` AS `pv_3` 
ON `pv_3`.`parameter_id` = 5 AND `pv_2`.`country_id` = 223 

JOIN `countries` ON `listings`.`country_id` = `countries`.`country_id` 
JOIN `countries_symbols` ON `countries`.`country_id` = `countries_symbols`.`country_id` 
JOIN `symbols` ON `countries_symbols`.`symbol_id` = `symbols`.`symbol_id` 

JOIN `listings_units` ON `listings`.`listings_unit_id` = `listings_units`.`listings_unit_id` 

JOIN `listings_units` AS `user_units` ON `user_units`.`listings_unit_id` = 2 


JOIN `listings_statuses` ON `listings`.`listings_status_id` = `listings_statuses`.`listings_status_id` 
LEFT JOIN `listings_statuses_countries` ON `listings_statuses`.`listings_status_id` = `listings_statuses_countries`.`listings_status_id` 
AND `listings_statuses_countries`.`country_id` = '223' 

JOIN `listings_categories` ON `listings_categories`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` ON `categories`.`category_id` = `listings_categories`.`category_id` 

JOIN `listings_categories` AS `lc` ON `lc`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` AS `c` ON `c`.`category_id` = `lc`.`category_id` 
LEFT JOIN `categories_countries` ON `c`.`category_id` = `categories_countries`.`category_id` AND `categories_countries`.`country_id` = '223' 

LEFT JOIN `listings_lettings`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `listings_lettings_rent_frequencies`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings_rent_frequencies`.`listings_lettings_rent_frequency_id` = `listings_lettings`.`listings_lettings_rent_frequency_id` 

LEFT JOIN `listings_lettings_rent_frequencies` AS `llrf` ON `llrf`.`listings_lettings_rent_frequency_id` = 2 
WHERE `listings`.`active` = 1 AND `listings`.`published_flag` = 1 AND 
`listings`.`listings_status_id` = 1 AND
`listings`.`lat` BETWEEN 51.333436956522 AND 51.681263043478 AND 
`listings`.`lng` BETWEEN -0.40717687879372 AND 0.15165687879372 AND 
st_within(`listings_coords`.`coords`, @poly) AND 

IF (`listings`.`transaction_type` = 2 || `listings`.`transaction_type` = 3, 
`listings`.`price_lettings` * `listings_lettings_rent_frequencies`.`months` >= `llrf`.`months` * 500,
TRUE
) AND 
IF(`listings_units`.`meters` IS NULL, TRUE, `listings`.`max_size` * `listings_units`.`meters` >= 100 * `user_units`.`meters`) AND 
`listings`.`country_id` = 223 

GROUP BY `listings`.`listing_id`  
ORDER BY `listings`.`created_at` 
LIMIT 10; 

As you can see, the first query grabs the polygon which then is used in polygon search through spatial function. The query executes in aprox 0.06sec.

However adding any ORDER BY clause results in a drastic performance drop and ends up executing in 17 seconds.

Now - I've done some research and I know this might be to do with either clustered indexes or just adding the right indexing in general, but after hours spent trying to either return just the ID's and then run another query to get the data or simply getting rid of the GROUP BY and removing the joins (which seemed to have worked at first but then another performance issues appeared when I joined a few tables to do a price search) - I gave up.

I tried to run a query without grouping and then group the results, like:

SELECT * FROM ( *the query* ) AS results GROUP BY listing_id

But I got pretty much the same results.

Is there a way to optimise the query so I could do both grouping and sorting?

Here is the EXPLAIN:

    |  1 | PRIMARY            | countries                          | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | Using index; Using temporary; Using filesort                              |
    |  1 | PRIMARY            | user_units                         | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses                  | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses_countries        | const  | PRIMARY,country_id                                                                | PRIMARY                              | 8       | const,const                                                      |    1 | Using index                                                               |
    |  1 | PRIMARY            | countries_symbols                  | ref    | PRIMARY,symbol_id                                                                 | PRIMARY                              | 4       | const                                                            |    1 | Using index                                                               |
    |  1 | PRIMARY            | symbols                            | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.countries_symbols.symbol_id                           |    1 | NULL                                                                      |
    |  1 | PRIMARY            | pv_2                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    2 | NULL                                                                      |
    |  1 | PRIMARY            | pv_1                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    3 | NULL                                                                      |
    |  1 | PRIMARY            | pv_3                               | ref    | parameter_id                                                                      | parameter_id                         | 4       | const                                                            |    4 | NULL                                                                      |
    |  1 | PRIMARY            | listings                           | range  | SEE BELOW                                                                         | compound                             | 20      | NULL                                                             | 2594 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
    |  1 | PRIMARY            | listings_units                     | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listings_unit_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_coords                    | eq_ref | PRIMARY,coords                                                                    | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_agents                        | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.auth_agent_id                                |    1 | NULL                                                                      |
    |  1 | PRIMARY            | branches                           | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.branch_id                                    |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_categories                | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | lc                                 | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_auth_agents_staff         | ref    | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | categories                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_categories.category_id                       |    1 | NULL                                                                      |
    |  1 | PRIMARY            | c                                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.lc.category_id                                        |    1 | NULL                                                                      |
    |  1 | PRIMARY            | auth_agents_staff                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_auth_agents_staff.auth_agents_staff_id       |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_staff                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.auth_agents_staff.auth_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | categories_countries               | eq_ref | PRIMARY,categories_countries_country_id_foreign                                   | PRIMARY                              | 8       | cp_staging.lc.category_id,const                                  |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_lettings                  | ref    | listings_lettings_listing_id_foreign                                              | listings_lettings_listing_id_foreign | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_lettings_rent_frequencies | eq_ref | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | cp_staging.listings_lettings.listings_lettings_rent_frequency_id |    1 | Using where                                                               |
    |  1 | PRIMARY            | llrf                               | const  | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | const                                                            |    1 | Using where                                                               |
    |  5 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  4 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  3 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  2 | DEPENDENT SUBQUERY | listings_categories                | eq_ref | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 8       | func,const                                                       |    1 | Using index                                                               |

5th column value for listings table (pulled out so that format of above table could be preserved) PRIMARY,listings_former_id_unique,listings_price_sales_index,listings_price_sales_min_index,listings_price_lettings_index,listings_price_lettings_min_index,listings_country_id_foreign,listings_agent_id_foreign,listings_listings_status_id_foreign,listings_listings_price_qualifier_id_foreign,listings_listings_unit_id_foreign,listings_listings_tenure_type_id_foreign,agents_staff_id,listings_listings_class_id_foreign_idx,listings_listings_location_type_id_foreign_idx,listings_listings_branch_id_foreign_idx,latlng,listings_city,listings_created_at,listings_country_created_at,listings_transaction_type,listings_listing_id_transaction_type,listings_listings_coords_id,listings_created_at_listing_id,compound

How to open a URL in an SSRS report in a new window? While the URLs keep changing in every record

I want to be able to open links in a new window in my SSRS report. The problem is these links are not the only things present in the record, and there is other data present. I keep seeing examples where they are considering only a link to be present in the record with no other data.

Also the data in the record includes HTML tags and I checked the option that says "HTML - Interpret HTML tags as styles" on the placeholder properties window.

Example of data in the record:

Hello! Welcome to google, the most used search engine. 
Click on www.google.com to go the website

So when I click on the www.google.com URL, it should open in a new window.

Edit: The URLs keep changing in every record.

I want more example on Optimization SQL Query

I want more example on Optimization SQL Query

"Advanced" SQL tutorial (t sql preferred)

So, I've recently started learning SQL, but I've hit a bit of a roadblock: nothing seems to be offered online besides the most basic topics. I used the tutorial at khan academy and GalaXQL but there doesn't really seem to be a site with more advanced problems than those contained within the previous two sites. Does anyone know of a place I can get used to more advanced queries / challenge myself further? I'm working within a T SQL environment, so anything related to that would be great, but I'll be happy with anything I can get.