Andrey, - Another idea for your problem is the one Kevin gave in the message following: ########################################################################################################################## SELECT * FROM t_route WHERE t_route.route_type_fk = 1 limit 4; This one scanned the t_route table until it found four rows that matched. It apparently didn't need to look at very many rows to find the four matches, so it was fast. SELECT * FROM t_route WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type = 2) limit 4; This one came up with an id for a route type that didn't have any matches in the t_route table, so it had to scan the entire t_route table. (Based on your next query, the subquery probably returned NULL, so there might be room for some optimization here.) If you had chosen a route type with at least four matches near the start of the route table, this query would have completed quickly. SELECT * FROM t_route, t_route_type WHERE t_route.route_type_fk = t_route_type.id AND type = 2 limit 4; Since it didn't find any t_route_type row which matched, it knew there couldn't be any output from the JOIN, so it skipped the scan of the t_route table entirely. -Kevin -------- Original Message --------
Kevin, thanks for your attention! I've read SlowQueryQuestions, but anyway can't find bottleneck... Here requested information: OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db installed in software RAID 0) Please also note that this hardware isn't dedicated DB server, but also serve as web server and file server. I have about 3 million rows in core_object, 1.5 million in plugin_plugin_addr and 1.5 million in plugins_guide_address. When there were 300 000+ objects queries works perfectly, but as db enlarge things go worse... # select version(); PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-4ubuntu8) 4.4.1, 64-bit ---postgresql.conf--- data_directory = '/mnt/fast/postgresql/8.4/main' hba_file = '/etc/postgresql/8.4/main/pg_hba.conf' ident_file = '/etc/postgresql/8.4/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/8.4-main.pid' listen_addresses = 'localhost' port = 5432 max_connections = 250 unix_socket_directory = '/var/run/postgresql' ssl = true shared_buffers = 1024MB temp_buffers = 16MB work_mem = 128MB maintenance_work_mem = 512MB fsync = off wal_buffers = 4MB checkpoint_segments = 16 effective_cache_size = 1536MB log_min_duration_statement = 8000 log_line_prefix = '%t ' datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' standard_conforming_strings = on escape_string_warning = off constraint_exclusion = on checkpoint_completion_target = 0.9 ---end postgresql.conf--- I hope this help! Any ideas are appreciated! On Apr 9, 12:44 am, Kevin.Gritt...@xxxxxxxxxxxx ("Kevin Grittner") wrote: > > Could you show us the output from "select version();", describe your > hardware and OS, and show us the contents of your postgresql.conf > file (with all comments removed)? We can then give more concrete > advice than is possible with the information provided so far. > > http://wiki.postgresql.org/wiki/SlowQueryQuestions > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performa...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance |
